vba: Find "xlpart"

[snjpverma]

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.

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.

 

[MARK858]

Just run another find with a string that you know will error and put a is nothing exit sub line afterwards at the end of the sub. Something like...

Dim xRng As Range
Set xRng = Columns(1).Find("nonsense string", , xlValues, xlPart, xlByRows, xlNext)
If xRng Is Nothing Then Exit Sub

Edit: see Fluff's answer as it doesn't matter if it is at the end of the sub, it's a much simpler way

 

[Fluff]

Just add this after the loop

   Cells.Find "", , , xlPart

 

[snjpverma]

Thanks to both of you for the answers.

Just add this after the loop

   Cells.Find "", , , xlPart

Fluff, would you please let me know what this code actually does?

It certainly serves the purpose (xlpart option selection) for me.

However, I just tried replacing "" with other common things. I thought the cursor will go to the cell containing the data.
However, it doesn't.

for e.g.
I used
Cells.Find 45, , , xlPart

and I wrote 45 in multiple cells, but the cursor didn't go select those cells.

Please explain.

 

[Fluff]

It just resets the LookAt to xlPart & the What to nothing & that's it.

 

[snjpverma]

It just resets the LookAt to xlPart & the What to nothing & that's it.

Great tip. Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[snjpverma]

I am curious to know why the code in post 1 works fine even when no match is found. Whereas the code in post 2 needs a statement IS NOTHING to handle the error when nothing is found.

 

[MARK858]

It is just that Microsoft decided that not finding anything to replace was a legitimate answer in replace and so coded under the bonnet the replace part of Find and Replace to handle it.