replace state names with state code abbreviations

[se7098]

is there an easy way to search a workbook for state names and replace with
state code abbreviations? the names are scattered throughout the workbook and
searching for each state individually and replacing with appropriate code is
time consuming and tedious. thanks.

 

[Gord Dibben]

Insert a new sheet named "List"

In A1:B50 enter state names and abbreviations

Run this macro.

Sub findandreplace()
Dim Vals As Range
Dim ws As Worksheet
Dim R As Range
Dim RR As Range

Set Vals = Sheets("List").Range("A1:B50")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "List" Then
Set R = ws.UsedRange
For Each RR In R
RR.Value = Application.VLookup(RR.Value, Vals, 2, False)
Next RR

End If

Next ws

End Sub


Gord Dibben MS Excel MVP

 

[se7098]

Thank you...PERFECT!

Insert a new sheet named "List"

In A1:B50 enter state names and abbreviations

Run this macro.

Sub findandreplace()
Dim Vals As Range
Dim ws As Worksheet
Dim R As Range
Dim RR As Range

Set Vals = Sheets("List").Range("A1:B50")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "List" Then
Set R = ws.UsedRange
For Each RR In R
RR.Value = Application.VLookup(RR.Value, Vals, 2, False)
Next RR

End If

Next ws

End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Happy to help.

Thanks for the feedback.


Gord

Thank you...PERFECT!