Wm Allen Smith
asked on
VBA to Find/Replace within a specified range in a Workbook
I have this code that converts state names to abbreviations for every iteration in a workbook:
I would like to narrow the focus where it only does this for a specific range ( ex. A:A or C:E) on a specified sheet.
Please advise.
Thanks!
wasmithpfs
Sub StateAbbrv_FindReplaceRange()
'PURPOSE: Find & Replace a list of text/values throughout entire workbook
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long
fndList = Array("Alabama", "Alaska", "Arizona", "Arkansas", "California", "Colorado", "Connecticut", "Delaware", "Florida", "Georgia", "Hawaii", "Idaho", "Illinois", "Indiana", "Iowa", "Kansas", "Kentucky", "Louisiana", "Maine", "Maryland", "Massachusetts", "Michigan", "Minnesota", "Mississippi", "Missouri", "Montana", "Nebraska", "Nevada", "New Hampshire", "New Jersey", "New Mexico", "New York", "North Carolina", "North Dakota", "Ohio", "Oklahoma", "Oregon", "Pennsylvania", "Rhode Island", "South Carolina", "South Dakota", "Tennessee", "Texas", "Utah", "Vermont", "Virginia", "Washington", "West Virginia", "Wisconsin", "Wyoming")
rplcList = Array("AL", "AK", "AZ", "AR", "CA", "CO", "CT", "DE", "FL", "GA", "HI", "ID", "IL", "IN", "IA", "KS", "KY", "LA", "ME", "MD", "MA", "MI", "MN", "MS", "MO", "MT", "NE", "NV", "NH", "NJ", "NM", "NY", "NC", "ND", "OH", "OK", "OR", "PA", "RI", "SC", "SD", "TN", "TX", "UT", "VT", "VA", "WA", "WV", "WI", "WY")
'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht
Next x
Dim wsTestSrc As Worksheet
'
End Sub
I would like to narrow the focus where it only does this for a specific range ( ex. A:A or C:E) on a specified sheet.
Please advise.
Thanks!
wasmithpfs
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're welcome and I'm glad I was able to help.
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015 and 2016
Experts Exchange Top Expert Visual Basic Classic 2012 to 2016
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015 and 2016
Experts Exchange Top Expert Visual Basic Classic 2012 to 2016
I just realized that there's a problem with the code and that is that LookAt:=xlPart should be changed to LookAt:=xlWhole. Otherwise for example "west virginia" would be changed to "west VA" rather than "WV".
ASKER
Thanks, Martin!
You're welcome. Do the cells that contain the state names only contain the state names?
ASKER
Martin Liss, yes, those cells will only contain state names.
That's good because otherwise I believe you'd need to replace the Find with a cell by cell search.
ASKER