replace state names with state code abbreviations

  • Thread starter Thread starter se7098
  • Start date Start date
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.
 
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
 
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...
 
Back
Top