How to specify what worksheet to use with find/with

Question

I want to search a range A1-A99 in a certain sheet (wsCaseinfo) for the word Overview. I get a 1004 error on the 'with' line.

The code is part of a larger code using 3 different sheets in 2 different files. Code cycles through 100 files, so something efficient would be appreciated. Many thanks for your help.

With wsCaseinfo.Range(Cells(1, 1), Cells(99, 1))
    Set cellx = .Find(what:="Overview", LookAt:=xlPart)
End With

Answer 1

You need to append the Cells() with the parent sheet:

With wsCaseinfo.Range(wsCaseinfo.Cells(1, 1), wsCaseinfo.Cells(99, 1))

Other wise the Cells() will refer to the active sheet and not the same sheet as the Range().

You can also nest a With in the first With

With wsCaseinfo
    With .Range(.Cells(1, 1), .Cells(99, 1))
        Set cellx = .Find(what:="Overview", LookAt:=xlPart)
    End With
End With