Thread: Find Creates Error on Second Iteration

Hello,

I am attempting to find a variable string in a range and then putting a formula in the cell 6 columns over. The entire process is in a loop so that the variable will change and a different cell will be found each time. The first iteration works perfectly, the second always bugs out with a Run time error 91: Object variable or With block variable not set.

I tried every combination of things I could think of, I hope someone here can help.

I am taking data from one workbook to another, so the data goes into destWS in destWB (destination). Data is copied to lines sequentially in destWS, so I have a destRow that tracks which line gets filled next and then adds 1 each iteration.

The Find code originally omitted activating cells and all that, but I changed it in hopes of it working and this is what's left. I cut out a lot of code above and below because it hopefully isn't related.

        Dim destWB As Workbook
        Dim destWS As Worksheet
        Dim destRow As Integer

        Dim currCode As String
        Dim foundCell As Range
        Dim foundAddy As String

        'lots of code in here

        destWS.Activate

        'MsgBox foundCell.Address
        
        Range("A49:A64").Select

        Selection.Find(What:=currCode, After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
        
        Set foundCell = ActiveCell
                      
        foundAddy = foundCell.Address
               
        destWS.Range(foundAddy).Offset(0, 6) = "=F" & destRow

        'rest of code

        destRow = destRow + 1

I'm hoping it's a simple quirk about find or objects that I don't know.

Thanks for your help!

I attached a dummy file with three worksheets. The first is lookup info. The second is destWS, and the third is the information that is in another book. The VBA is in the workbook as well (it ain't pretty).

Everything worked until I added the find lines. The data reads in just fine from the other worksheet, and the rest works well.

I really think I am messing up something with the objects or ranges. They work fine the first iteration, and bug out the second.

Thanks for your help.

Oh I found it. Thanks for making me look through it all!