Agree with Martin on this one, Find/Replace is easiest method...
If you ever wanted to create a macro that would do the same thing, just turn on the macro recorder and take the steps you wold need to perform the replacement manually, then stop the recorder and check out the VBA to see how it is set up.
Below, I did just that:
Sub replace_recorded()
Range("B11:N50").Select
Selection.replace What:="(SWP)*", Replacement:="(SWP)", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Or, you could loop through each cell in the range and do the same...
Sub replace_looped()
Dim r As Long, c As Long
For r = 11 To 50
For c = 2 To 14
ActiveSheet.Cells(r, c).replace What:="(SWP)*", Replacement:="(SWP)", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next c
Next r
End Sub
Lots of ways to do these things... but I would still go with the old Find/Replace as Martin suggested.
- Moo
Bookmarks