VBA Run-Time Error 91 on Second Iteration

Question

I wrote some code to allow me to create invoices by extracting data from a large spreadsheet of data and populating a blank invoice with the information.

I need to sort the invoices by the Account ID Number, Environment, and Product Name. The Usage Type and associated costs fill up a table.

I had a working program to create the invoices for each Environment, but I now need to separate based on Account ID as well.

The code I have works fine for the first iteration of exLoop. I get the right invoices for the first Account ID Number. When it moves onto the second iteration of ExLoop, it gives me Run-Time Error 91: Object variable or With block variable not set. I have tried this so many ways and I just don't understand why it's working the first time and not the second time. Here is the code with the error line indicated:

Sub CreateInvoices()

Dim bookName, sheetName, accountId, accountName As String
Dim usageType, totalCost, productCode, productName, environment As String
Dim myDate, path, myFileName As String
Dim invoiceRow, tempRow As Long
Dim mainLoop, outerLoop, exLoop, row As Long
Dim firstAccountName, lastAccountName, firstEnviroName, lastEnviroName, firstProductName, lastProductName As Long
Dim accountRng, enviroRng, prodRng As Range
Dim accountList, enviroList, prodList As Object
Dim accountNameCount, enviroNameCount, prodNameCount, lastRow As Integer

'initialize data workbook and worksheet names
bookName = ActiveWorkbook.Name
sheetName = ActiveSheet.Name
'initialize first row of environment and product names
firstAccountName = 2
firstEnviroName = 2
firstProductName = 2

'find last row of data
lastRow = Sheets(sheetName).Range("A" & Rows.Count).End(xlUp).row

'sort by usage type, then product name, then environment, then accountId
Range("P1").Columns(16).Sort Key1:=Range("P1"), Order1:=xlAscending, Header:=xlYes
Range("N1").Columns(14).Sort Key1:=Range("N1"), Order1:=xlAscending, Header:=xlYes
Range("AE1:AE" & lastRow).Sort Key1:=Range("AE1"), Order1:=xlAscending, Header:=xlYes
Range("C1").Columns(3).Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes

'determine the number of unique linked accounts
Set accountList = CreateObject("Scripting.Dictionary")
For Each accountRng In Range("C2:C" & lastRow)
    If Not accountList.Exists(accountRng.Value) Then accountList.Add accountRng.Value, Nothing
Next
accountNameCount = accountList.Count

For exLoop = 0 To accountNameCount - 2

    'Activate data sheet
    Workbooks(bookName).Sheets(sheetName).Activate
    'Find last row with current account name
    lastAccountName = Sheets(sheetName).Range("C2:C" & lastRow).Find(What:=accountList.keys()(exLoop), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlRows, SearchDirection:=xlPrevious).row 'THIS IS WHERE THE ERROR HAPPENS ON THE SECOND ITERATION OF EXLOOP

    'determine the number of unique environments
    Set enviroList = CreateObject("Scripting.Dictionary")
    For Each enviroRng In Range("AE" & firstAccountName & ":AE" & lastAccountName)
        If Not enviroList.Exists(enviroRng.Value) Then enviroList.Add enviroRng.Value, Nothing
    Next
    enviroNameCount = enviroList.Count

    'outer loop controls us making a new groups of invoices for each environment
    For outerLoop = 0 To enviroNameCount - 1

        'Activate data sheet
        Workbooks(bookName).Sheets(sheetName).Activate
        'Find last row with current environment
        lastEnviroName = Sheets(sheetName).Range("AE" & firstAccountName & ":AE" & lastAccountName).Find(What:=enviroList.keys()(outerLoop), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlRows, SearchDirection:=xlPrevious).row

        'determine the number of unique product names
        Set prodList = CreateObject("Scripting.Dictionary")
        For Each prodRng In Range("N" & firstEnviroName & ":N" & lastEnviroName)
            If Not prodList.Exists(prodRng.Value) Then prodList.Add prodRng.Value, Nothing
        Next
        prodNameCount = prodList.Count

        'main loop controls us making a new invoice for each product name
        For mainLoop = 0 To prodNameCount - 1

'MORE CODE HERE TO EXTRACT THE DATA, POPULATE THE TEMPLATE AND SAVE THE FILE

        'update first product name row to the next product name
        firstProductName = lastProductName + 1

        Next mainLoop

    'update first environment row to the next environment
    firstEnviroName = lastEnviroName + 1

    Next outerLoop

'update first account row to next account
firstAccountName = lastAccountName + 1

Next exLoop

End Sub

Answer 1

This isn't a fix, but may help you to debug the code to find out where the problem is occurring.

This error is quite common when a number of methods and properties are chained together in VBA. If one of the methods or properties doesn't return a valid object reference, then any property or method that you call on that object will fail. If you choose Debug when the error occurs (rather than End), you can hover over each part of the command-line to see which one isn't returning the expected value.

Sometimes it is easier to find the problem by breaking down the line of code and specifically setting each object variable:

Dim sheet As Worksheet
Dim r As Range
Dim rFound As Range
Dim search As Variant

Set sheet = Sheets(sheetName)
Set r = sheet.Range("AE" & firstAccountName & ":AE" & lastAccountName)
search = enviroList.keys()(outerLoop)
Debug.Print search
Set rFound = r.Find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlRows, SearchDirection:=xlPrevious)
lastEnviroName = rFound.Row

I'd suspect that the most likely issue would be that the Find is returning Nothing and therefore the Row property would fail. However, the steps above should help you confirm whether this is the case.

Once you know where the problem happens, it is easier to work out why.