macro not running

[gwh]

Hi,

I found the following macro but it doesn't seem to run:

Public Sub RemoveTrailingSpacesAndFullStops()
Dim rCell As Range
Dim sTemp As String
On Error Resume Next 'in case none found
For Each rCell In Cells.SpecialCells( _
xlCellTypeConstants, xlTextValues)
sTemp = RTrim(rCell.Value)
If sTemp Like "*." Then _
sTemp = RTrim(Left(sTemp, Len(sTemp) - 1))
rCell.Value = sTemp
Next rCell
On Error GoTo 0
End Sub

I just pasted it into a new module but it appears red indicating a
syntax error and won't run. Can anyone tell me what might be wrong?

Thanks

 

[Ken Johnson]

Hi,

I found the following macro but it doesn't seem to run:

Public Sub RemoveTrailingSpacesAndFullStops()
Dim rCell As Range
Dim sTemp As String
On Error Resume Next 'in case none found
For Each rCell In Cells.SpecialCells( _
xlCellTypeConstants, xlTextValues)
sTemp = RTrim(rCell.Value)
If sTemp Like "*." Then _
sTemp = RTrim(Left(sTemp, Len(sTemp) - 1))
rCell.Value = sTemp
Next rCell
On Error GoTo 0
End Sub

I just pasted it into a new module but it appears red indicating a
syntax error and won't run. Can anyone tell me what might be wrong?

Thanks

Hi,

It works perfectly on both my Mac and PC.

Maybe there is a line break in the code on your computer.

What if you copy and paste this version with all of the tabs removed to
reduce the risk of a line break occuring?...

Public Sub RemoveTrailingSpacesAndFullStops()
Dim rCell As Range
Dim sTemp As String
On Error Resume Next 'in case none found
For Each rCell In Cells.SpecialCells( _
xlCellTypeConstants, xlTextValues)
sTemp = RTrim(rCell.Value)
If sTemp Like "*." Then _
sTemp = RTrim(Left(sTemp, Len(sTemp) - 1))
rCell.Value = sTemp
Next rCell
On Error GoTo 0
End Sub

Ken Johnson

 

[gwh]

Thanks for that,

The macro is working now but it hasn't seemed to have solved my
original problem. I have an excel database that I'm using in a envelope
mail merge in Word. Some of the entries don't have titles and others do
and when I do the merge there seems to be a space before the first name
in the entries that don't have titles (ie. (space)John Smith) There's
no space though in the entries that do have titles (which is correct).
I assumed that this space was due to something in the excel spreadsheet
which is why I ran the macro but this obviously isn't the problem.

Are you able to tell me what might be happening? Or should I be posting
this in the Word forum?