Find nothing but still run through if statement-CodePudding

I'm trying to write a sub for my excel table, the macro read open another workbook then find a certain value (lz) of a column, read all relevant information, then proceed to find the next row with the same column value lz and do the same thing until its found result is the same as the first found result. That is when I know I have read all information from that certain lz and can jump to the next lz

So I tried this code so far:

        Set foundCell = ActiveWorkbook.Worksheets("New1").Range("F:F").Find(What:=lz, _
            LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, After:=Range("F10000"), _
            SearchDirection:=xlNext, SearchFormat:=False, MatchCase:=False)
            
        
        If Not foundCell Is Nothing Then
            firstFound = foundCell.Address
            Do
                'do something with the relevant data
                Set foundCell = ActiveWorkbook.Worksheets("New1").Range("F:F").FindNext(foundCell)
            Loop While Not foundCell Is Nothing And firstFound <> foundCell.Address
        Else
            flag = False
        End If

It works for about 2 loops then when it comes to a specific lz, it crash and the error is : "object variable or with block variable not set". When I try to debug to see where the problem is, i found out that at the last step before it crashed, the foundCell is Nothing but somehow it still goes through the big If at the beginning and make the error. I expect it to go to the else statement down below when this happens

CodePudding user response：

Typical VBA pitfall: When foundCell is Nothing (because nothing is found), your condition to stop the While-Loop is fulfilled, but VBA will check the second part of that condition anyhow, and as foundCell is Nothing, you will get a runtime error for the foundCell.Address-part of the statement.

My suggestion: Jump out of the loop with an Exit-statement. There are other ways to handle that, but what is important is that you don't use the foundCell.Address-property when foundCell can be Nothing, and therefore you need two separate checks.

firstFound = foundCell.Address
Do
    'do something with the relevant data
    Set foundCell = ActiveWorkbook.Worksheets("New1").Range("F:F").FindNext(foundCell)
    If foundCell Is Nothing Then Exit Do

Loop While firstFound <> foundCell.Address

CodePudding user response：

Just make the IF statement inside the loop