Using Find method in For Each Loop - Object Variable or With Block Variable Not Set

[BrandynBlaze]

Hi There,

I'm having a hard time figuring out why I'm getting the subscript out of range error on the following code.

All I'm trying to do is use the search terms I have stored in the range "SearchTerm" to find any values that are in the range ToSearch. The lines outside of the the For Each Loop is the same idea and it works just fine, but something about the way my For Each loop is working is causing it to throw an error. I set the range of cell to be the first cell of the SearchTerm range to approximate what is happening in the loop.

I've moved some things around to troubleshoot the "Variable Not Set" error, and all I get is a subscript out of range error instead.

Any ideas what might be happening here?

    Set cell = ActiveSheet.cells(1,1)

    Set WasFound = ToSearch.Find(cell.Value)
    MsgBox WasFound.Text
    cell.clear

    For Each cell In SearchTerm
    
        Set WasFound = ToSearch.Find(cell.Value)
        MsgBox WasFound.Text
                
    Next cell

 

[Norie]

I think it might be the Find that's causing the problems rather than the loop.

For one thing, you are missing a lot of arguments from Find and when you do that the settings default to what was used in the last Find whether performed manually or through code.