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