Measure Text and insert underscore.
 

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

Measure Text and insert underscore.
 

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.
--
Gary's Student


"Rahul Gupta" wrote:

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

Measure Text and insert underscore.
 

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.

"Gary''s Student" wrote:

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.
--
Gary's Student


"Rahul Gupta" wrote:

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

Measure Text and insert underscore.
 

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.

"Rahul Gupta" wrote:

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.

"Gary''s Student" wrote:

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.
--
Gary's Student


"Rahul Gupta" wrote:

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

Measure Text and insert underscore.
 

=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




Rahul Gupta wrote:

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.

"Rahul Gupta" wrote:

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.

"Gary''s Student" wrote:

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.
--
Gary's Student


"Rahul Gupta" wrote:

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

--

Dave Peterson