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

On Fri, 24 Jul 2009 08:01:01 -0700, se7098
wrote:

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.

Thank you...PERFECT!

"Gord Dibben" wrote:

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

On Fri, 24 Jul 2009 08:01:01 -0700, se7098
wrote:

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.

Happy to help.

Thanks for the feedback.


Gord

On Fri, 24 Jul 2009 14:40:01 -0700, se7098
wrote:

Thank you...PERFECT!

"Gord Dibben" wrote:

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

On Fri, 24 Jul 2009 08:01:01 -0700, se7098
wrote:

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.