VBA: error 91 Object variable or with block variable not set

Question

I faced a weird issue of VBA error 91. I saw many other people have this problem because they didn't use keyword "Set" for object, whereas that is not my case.

Following is my code:

Dim eventWS As Worksheet
Set eventWS = Worksheets("Event Sheet")

Dim eventRange As Range
Set eventRange = eventWS.Columns("A:A").Find(240, , xlValues, xlWhole)

If Not eventRange Is Nothing Then 

      Dim eventFirstAddress As String 
      eventFirstAddress = eventRange.Address 

      Do 
         If eventWS.Range("L" & eventRange.Row).Value = busId Then 
              If commuter = True Then 
                 Count = Count + Affected(eventWS.Range("Q" & eventRange.Row).Value)
              Else
                 Count = Count + 1
              End If 
         End If 
         MsgBox("Before call move next: " & eventRange.Row )
         Set eventRange = eventWS.Columns("A:A").FindNext(eventRange)
         MsgBox("After call move next: " & eventRange.Row )
      Loop While Not eventRange Is Nothing And eventRange.Address <> eventFirstAddress 
End If

Affected() is a function I can call to do internal processing. And if I removed this "Count = Count + Affected(....)", the code was working fine. If I added it, "Loop While " would throw error 91. If I added a message box to print out the row number before and after moving eventRange, it turned out that "MsgBox("After call move next: " & eventRange.Row)" would throw error 91.

Hence, I'm confuse whether the issue is caused by the internal function or the eventRange now. Hope someone can point my mistakes out. Thank you very much.

Following are the codes of internal function:

Function Affected(markerId As Integer) As Integer

'initialized return value'
AffectedCoummters = 0

'get total financial sheets'
Dim totalFinancial As Integer
totalFinancial = 0
For Each ws In Worksheets
    If InStr(ws.Name, "Financial") > 0 Then
        totalFinancial = totalFinancial + 1
    End If
Next

Dim i As Integer
'run through all financial sheets'
For i = 1 To totalFinancial

    'get current financial sheet'
    Dim financialWS As Worksheet
    Set financialWS = Worksheets("Financial Sheet" & i)

    'get total rows of current operation sheet'
    Dim rowSize As Long
    rowSize = financialWS.Range("A" & financialWS.Rows.Count).End(xlUp).Row

    'if reach the maximum number of rows, the value will be 1'
    'reInitialize rowSize based on version of Excel'
    If rowSize = 1 Then
        If Application.Version = "12.0" Then
            'MsgBox ("You are using Excel 2007")'
            If InStr(ThisWorkbook.Name, ".xlsx") > 0 Then
                rowSize = 1048576
            Else
                'compatible mode'
                rowSize = 65536
            End If
        ElseIf Application.Version = "11.0" Then
            'MsgBox ("You are using Excel 2003")'
            rowSize = 65536
        End If
    End If

    'filter by marker id first inside current financial sheet'
    Dim findMarker As Range
    Set findMarker = financialWS.Columns("K:K").Find(markerId, , xlValues, xlWhole)

    'if found any given marker id'
    If Not findMarker Is Nothing Then

        Dim firstAddress As String
        firstAddress = findMarker.Address

        'check all matched marker id'
        Do

                    AffectedCommuters = AffectedCommuters + financialWS.Range("O" & findMarker.Row).Value

            'move to next'
            Set findMarker = financialWS.Columns("K:K").FindNext(findMarker)

        Loop While Not findMarker Is Nothing And findMarker.Address <> firstAddress

    End If

Next i

End Function

Can we see your function too? At min. the lines of definition and one which returns result. Did you checked in Immediate if you function works fine and returns result as expected? — Kazimierz Jawor
Sure, KazJaw. I post the Affected Function. I useded it for other cases, it worked fine. — ShadowScorpion
There is nothing unusual in your code. Just check if eventWS is what it should be. I would suggest debugging with F8 for one iteration. — Kazimierz Jawor
Thanks again, KazJaw. That' the reason I felt confuse, it should worke! — ShadowScorpion
Yes, could be... you could set additional condition statement If Not eventRange Is Nothing Then after find next and Exit Do if nothing is found. Than change you loop conditions which seems to not working properly — Kazimierz Jawor

KacireeSoftware KacireeSoftware · Accepted Answer · 2013-04-14T15:19:33

Sorry I dont have enough rep to comment so I have to answer here :( Just want to say that although it is standard procedure to use

 Loop While Not eventRange Is Nothing And eventRange.Address <> eventFirstAddress

in this type of procedure, if eventRange is actually Nothing, the line will throw Error 91, because eventRange.address does not exists. What this means is that once you have found something, you can't modify the row in such a way that it will not be found again using .findnext.

After you exit the do...loop, you can modifiy the range to suit... Perhaps you want to use an array to hold all the rows from your .find...findnext results, and then manipulate them after the Do...loop

VBA: error 91 Object variable or with block variable not set

1 Answers