'Subscript Out Of Range' error when trying CountIf function

Viewed 53

I'm creating a sub-routine which conducts a CountIf to determine whether the ToolWB account numbers appear in the assetRegisterWB.

I'm getting a "Subscript out of Range" error at the CountIf Worksheet function towards the bottom. I think this has something to do with the .Value but cannot be sure.

Option Explicit

Sub IsDatasetInAR()

    Dim a As Range
    Dim accColumn As Long
    Dim AccColumnLetter As String

    sheet = "Listing 1 Reconciliation"

    For Each wb In Application.Workbooks
        If wb.Name Like Tool & "*" Then
           Set ToolWB = Workbooks(wb.Name)
        End If
    Next wb
    
    For Each wb In Application.Workbooks
        If wb.Name Like assetRegister & "*" Then
           Set assetRegisterWB = Workbooks(wb.Name)
        End If
    Next wb
    
    With assetRegisterWB.Worksheets(1).Rows(1)
        Set a = .Find("Mortgage/Loan Acc Number", LookIn:=xlValues)
        accColumn = a.Column
        AccColumnLetter = Split(Cells(1, accColumn).Address, "$")(1)
    End With
    
    lastRow = ToolWB.Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRow
    
        ToolWB.Worksheets(sheet).Cells(i, 3).Value = _
        Application.WorksheetFunction.CountIf(assetRegisterWB.Worksheets(1). _
        Range(AccColumnLetter & ":" & AccColumnLetter), ToolWB.Worksheets _
        (sheet).Cells(i, 1).Value)
    
    Next i

End Sub
1 Answers

Silly mistake - I forgot to assign Tool & assetRegister values and therefore did not pick up any workbook:

Tool = "Reconciliations Tool"
assetRegister = "Asset Register Extract"

(FYI Tool & assetRegister have been publicly declared)

Related