Need assistance with vba Find method that does not find a string does exist.

[OaklandJim]

As always this has to be something basic that I am missing...Code below shows what I have. I am looking for a specific string in a worksheet. Below the code shows where the string is in the target file. What the heck am I doing wrong? Call to Find throws up an "Automation error".

Sub AddNames(pwsToolsWorksheet As Worksheet)
    
'   parameter pwsToolsWorksheet is the worksheet into which names are added.
    
'   Cell containing the header Tool Description
    Dim rCellFound As Range
    
'   Cells containing Tool Descriptions.
    Dim rDataCells As Range
    
'   The row of the last entry in the Tool Descriptions data.
    Dim iLastCellRow As Long
    
'   The column of the last header in the Tool Descriptions data.
    Dim iLastColumn As Long
            
'   Look for the text Tool Description in the worksheet
    'On Error Resume Next
    Set rCellFound = pwsToolsWorksheet.Cells.Find(What:="Tool Description", LookAt:=xlWhole)
    'On Error GoTo 0

The string sought is in cell A1!

Other3.xlsm
	A	B
1	Tool Description	Tool Part Number
2	.002R PH HORN Bore Bar	R105.1813.005.0.25 TH35
3	.008R Boring Bar Insert	R105.1833.3.6 TH35
Tooling

 

[mumps]

Try:

Sub AddNames()
    Dim rCellFound As Range, rDataCells As Range, iLastCellRow As Long, iLastColumn As Long
    Set rCellFound = Sheets("Tooling").Cells.Find(What:="Tool Description", LookAt:=xlWhole)
    MsgBox rCellFound
End Sub

 

[OaklandJim]

That was not quite the answer but it was helpful. Your post got me to take a second look and I was calling that sub in the wrong place in the caller sub such that the worksheet parameter was not being set before the call. Thanks very much!

 

[mumps]

You are very welcome.