replace state names with state code abbreviations

S

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.
 
G

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
 
S

se7098

Thank you...PERFECT!

Gord Dibben said:
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
Click to expand...
 
Top