substituting a number for a symbol

  • Thread starter Thread starter Mat
  • Start date Start date
M

Mat

Greetings

Would anyone be able to tell me how to solve the following:

I want to enter a figure in a cell and have it changed to the corresponding
number of ticks. For example, if I enter 3 in cell A1 I want 3 ticks to
appear in cell A1.

Any ideas?

Many thanks in advance

Mat
 
=rept("|",a1)

But for something neater, try this:

=REPT(REPT(CHAR(134),4)&" ",INT(A1/5))&REPT("|",MOD(A1,5))
 
To have the check marks appear in the same cell as the number, you could
use a worksheet change event, e.g.:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$A$1" Then
If IsNumeric(Target.Value) Then
Target.Formula = "=REPT(Char(252)," & Target.Value & ")"
Target.Font.Name = "Wingdings"
Else
Target.Font.Name = "Arial"
End If
End If
End Sub

The code is stored on the worksheet module (right-click the sheet tab,
and choose View Code)
 
Oops, missed that same cell thingy.

Debra said:
To have the check marks appear in the same cell as the number, you could
use a worksheet change event, e.g.:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$A$1" Then
If IsNumeric(Target.Value) Then
Target.Formula = "=REPT(Char(252)," & Target.Value & ")"
Target.Font.Name = "Wingdings"
Else
Target.Font.Name = "Arial"
End If
End If
End Sub

The code is stored on the worksheet module (right-click the sheet tab,
and choose View Code)
Click to expand...
 
Right click on sheet tab>view code>insert this>SAVE
Use fixit if it stops working

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
If Application.Intersect(Target, Range("g10:g20")) _
Is Nothing Then Exit Sub
With Target.Font
.Name = "Wingdings 2"
.Bold = True
.ColorIndex = 3
End With
x = Target
For i = 1 To x
Application.EnableEvents = False
mystring = mystring & "P"
Next
Target = mystring
Application.EnableEvents = True
End Sub

Sub fixit()
Application.EnableEvents = True
End Sub
 
Back
Top