VBA .FindNext while setting other object in loop failing

Question

I'm attempting to execute a do-while loop with the .FindNext method to find multiple instances in a column. On its own, it works fine. However. when trying to find a corresponding value in another sheet within the same loop, it throws the 'Object variable or With block variable not set' error (91).

If Not found_title Is Nothing Then

    first_cell_address = found_title.Address

    Do
        Set found_url = url_lookup.Find(found_title.Offset(0, -4).Value)
        Debug.Print ("looped")
        Set found_title = title_lookup.FindNext(found_title)

    Loop While first_cell_address <> found_title.Address

End If

When I comment out 'set found_url...' it works fine, but when including it, the 'found_title' object has been set to nothing by the time it reaches the while condition for the first time. As the FindNext method wraps around the range, I can't see why this is being set to nothing when there must be at least one of them to enter the if statement in the first place.

SOLUTION:

As per rory's response, the change was:

Set found_title = title_lookup.Find(title, After:=Range(found_title.Address))

Rory Rory · Accepted Answer · 2017-10-31T12:50:30

If you nest one Find within another, you can't use FindNext on the outer one. You have to repeat the original Find and specify the After argument as the previous found cell.

VBA .FindNext while setting other object in loop failing

1 Answers