Measure Text and insert underscore.

[Guest]

Hello,

I have a requirement that for a specfic font whenever I am provided some
text in random cell i measure the text and insert _ symbol.

for ex.
____________________
My Name is Rahul Gupta
____________________

Please help.

Regards,
Rahul

 

[Guest]

Here is some demo code that you can modify as needed:

Sub gupta()
Dim s As String
For Each r In Selection
If r.Font.Name = "Arial" Then
s = r.Value
l = Len(s)
If l > 2 Then
s = Left(s, 2) & "_" & Right(s, l - 2)
r.Value = s
End If
End If
Next
End Sub

Select a range of cells and run the macro. It looks for cells with the
Arial font and insters an underscore after the second character.

 

[Guest]

Thanks Gary,

Its not an answer to my question, but gave me an idea to solve my query.

Thanks again, but sorry since i am not marking it as ans to the query.

Regards,
Rahul.

 

[Guest]

Hello Gary,

I modified the code to look like below, but its giving the error, but i am
not sure why. Can you help?

Sub gupta()
Dim s As String
For Each r In Selection
l = Len(r.Value)
s = Rept("_", l)
r.Offset(-1, 0).Value = s
r.Offset(1, 0).Value = s
Next
End Sub


Regards,
Rahul.

 

[Dave Peterson]

=rept() is a worksheet function. You can use String() in VBA.

Option Explicit
Sub gupta()
Dim s As String
Dim r As Range
Dim l As Long

For Each r In Selection
l = Len(r.Value)
s = String(l, "_")
r.Offset(-1, 0).Value = s
r.Offset(1, 0).Value = s
Next r
End Sub