jeffcoleky
Active Member
- Joined
- May 24, 2011
- Messages
- 274
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Sheets("ListFilter").Columns("M:V") ' change to columns to replace string
Application.DisplayAlerts = False
.Replace What:=" Lane", Replacement:=" Ln", SearchOrder:=xlByColumns, MatchCase:=True
.Replace What:=" Road", Replacement:=" Rd", SearchOrder:=xlByColumns, MatchCase:=True
.Replace What:=" Avenue", Replacement:=" Ave", SearchOrder:=xlByColumns, MatchCase:=True
.Replace What:=" Trace", Replacement:=" Trce", SearchOrder:=xlByColumns, MatchCase:=True
.Replace What:=" Circle", Replacement:=" Cir", SearchOrder:=xlByColumns, MatchCase:=True
.Replace What:=" Boulevard", Replacement:=" Blvd", SearchOrder:=xlByColumns, MatchCase:=True
.Replace What:=" Court", Replacement:=" Ct", SearchOrder:=xlByColumns, MatchCase:=True
[B].Replace What:=" Street", Replacement:=" St", SearchOrder:=xlByColumns, MatchCase:=True[/B]
End With
Application.DisplayAlerts = True
End Sub
Unfortunately, this macro will change the data if the "What:=" text is found ANYWHERE in the cell. Is there any way to easily modify specific lines so that it will only replace the text if the text is found at the END of the cell?
For example:
"123 Main Street" <-- No problem changing to "123 Main St"
But...
"555 Third Street Road" >> Converts to >> "555 Third St Rd" (A problem).