I have found a vba script to find and replace words but the script replaces the words in the whole workbook, I would like it to replace the words only in a specific sheet. This is the code I have found.
Sub Multi_FindReplace()
'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("United States", "New York")
rplcList = Array("US", "NY")
'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
End Sub
Just remove the sheet loop and specify the sheet name in the code where it says "Sheet1".
Sub Multi_FindReplace()
Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long
fndList = Array("United States", "New York")
rplcList = Array("US", "NY")
Set sht = ActiveWorkbook.Worksheets("Sheet1") 'this is the relevant worksheet
For x = LBound(fndList) To UBound(fndList)
sht.Cells.Replace what:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next x
End Sub
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments