Multi-Workbook .Find

[HCGaudior]

Hello,

I am working on consolidating and interpreting financial data that occurs quarterly. Each quarter, we are provided a spreadsheet with thousands of rows of data spread over dozens of sheets which I need to consolidate together to make projections. I have made a separate spreadsheet called 'Consolidator.xlsm' that filters out the junk data. For this case, I am examining what projects employees spent time on so that I can determine if actuals are matching the budget. I have most of the code working, but I get a Run-time error '91': Object variable or With block variable not set any time I try to reference the variable. If I comment out the MsgBox, the code runs without error. Here is the section of code that is giving me issues:

Dim wsExist As Boolean                      'Variable to store if the worksheet exists in the actuals workbook t/f
Dim wbQ1Actuals As Workbook                 'Variable to store workbook with quarter 1 actuals
Dim searchRange As Range                    'Variable to store the range to search for budget data
Dim firstDataCell As Range                  'Variable to store first data cell
Dim lastDataCell As Range                   'Variable to store the last data cell
Dim Budget As Integer                       'Variable to store the current task budget number
Dim notIn414 As String                      'Variable to store employees the VB could not find in 414
Dim firstName As String                     'Variable to store employee's first name
Dim lastName As String                      'Variable to store employee's last name

Set wbQ1Actuals = Workbooks("*the spreadsheet*.xlsx")
                                            'Store worbook with Q1 actuals into variable
Set wbConsolidator = Workbooks("Consolidator.xlsm")
                                            'Store consolidation worbook into variable

    If wsExist = True Then                     'Check if wsExist is true
        wbQ1Actuals.Worksheets(tabName).Activate
        Set searchRange = Range("A1", Range("A65536").End(xlUp))
        Set firstDataCell = searchRange.Find(Budget, LookIn:=xlValues, lookat:=xlWhole)
                                                'Find the first row with actuals percentages
        Set lastDataCell = searchRange.Find("Total:", LookIn:=xlValues, lookat:=xlWhole)
                                                'Find the sum row to track last row with actuals percentages
        
    Else
        notIn414 = notIn414 & lastName & "," & firstName & ":"
                                                'Write the employee's name to the log to check in the next budget
    End If   

wbConsolidator.Activate                         'Activate the main spreadsheet

MsgBox lastDataCell.Row                         'Verify the last cell reference is correct

I think the VBA tries to store the cell in the wbQ1Actuals workbook, but I'm not sure if this is the case. I get the error regardless of position relative to 'wbConsolidator.Activate'. If you need more information, please let me know. I'm sure I left out some critical detail.

 

[HCGaudior]

Additional Information:

I modified my MsgBox to the following VBA:

If lastDataCell Is Nothing Then
    MsgBox "The variable is blank."
Else
    MsgBox lastDataCell
End If

I get the message that the variable is blank.