For Loop Append to Array-CodePudding

I'm iterating through the "AccountName" column and then append the value 5 rows to the left (DisplayID) to an array or list if the Account Name value contains "National" in it's title. I will then use that array as a criteria to filter the range so that I only have the transactions that interacted with that account.

This is what I have so far, however I'm getting errors regarding the Array. I get a Run-time Error '13' type mismatch error on the "ReDim Preserve Criteria1(Row)" line.

Sub FindBankAccount()
Dim LastRow As Long
Dim Acc_Name_Row As Long, Acc_Name_Column As Long
Dim rng As Range
Dim Criteria1()


With Sheet4
'Finding Relevant Rows & Columns
LastRow = .Cells.End(xlDown).Row
Acc_Name_Row = .Cells.Find(What:="AccountName").Row
Acc_Name_Column = .Cells.Find(What:="AccountName").Column
Acc_Name_Offset = Acc_Name_Row   1

Set rng = .Range(Cells(Acc_Name_Offset, Acc_Name_Column), Cells(LastRow, Acc_Name_Column))
End With

For Each Row In rng.Rows
    If Row.Value Like "National" & "*" Then
        Testing = Row.Cells.Offset(0, -5)
    ReDim Preserve Criteria1(Row)
    Criteria1(Row) = Testing
    End If

Next

End Sub

Basically I want to go from this:

To This: i.e. I'll be selecting GJ000021 as a filter criteria as the transaction involved interaction with the "National Bank Account" account name.

CodePudding user response：

The problem is, Row is not an integer/long, it's a Range object ...

You need to define Row as a Range (Dim Row as Range) and then change your ReDim to be like this ...

ReDim Preserve Criteria1(Row.Row)

Personally, I'd change the variable names but it's up to you.

Also, throw Option Explicit at the top of your module, it will make sure you define your variables correctly, this is optional though.

Also, with what you're doing, your array is going to be full of empty items.

This may serve you better ...

Dim lngIndex as long    

For Each Row In Rng.Rows
    If Row.Value Like "National" & "*" Then
        Testing = Row.Cells.Offset(0, -5)
        
        ReDim Preserve Criteria1(lngIndex)
        Criteria1(lngIndex) = Testing
        
        lngIndex = lngIndex   1
    End If
Next