I have a macro that does a find xlwhole on an array of words and replaces then accordingly.
Below is the code from a website that I modified to suit my needs.
The macro works absolutely fine.
However, I want to add a small line which can set my "Find (LookAt)" back to Xlpart.
Because apart from this macro I never use xlwhole to find.
So every time I run the macro, I have to open the option in Find manually and mark the match entire cell contents.
please suggest.
Below is the code from a website that I modified to suit my needs.
VBA Code:
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("Canada", "United States", "Mexico")
rplcList = Array("CAN", "USA", "MEX")
'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:=xlwhole, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht
Next x
End Sub
The macro works absolutely fine.
However, I want to add a small line which can set my "Find (LookAt)" back to Xlpart.
Because apart from this macro I never use xlwhole to find.
So every time I run the macro, I have to open the option in Find manually and mark the match entire cell contents.
please suggest.