For Loop Append to Array

Question

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.

Skin · Accepted Answer · 2022-01-20 02:05:17Z

2

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

edited Jan 20, 2022 at 2:05

answered Jan 20, 2022 at 1:43

Skin

11.7k3 gold badges16 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jered Over a year ago

Doesn't this just append the row number to the array? I'm wanting the value of the DisplayID (I defined this in the 'Testing' variable) to be added to the array.

Skin Over a year ago

The ReDim resizes the array, it's not assigning any value to that item in the array at that point, that's your next line of code. On that, I'm going to change my answer and enhance to what I think you should do.

Collectives™ on Stack Overflow

For Loop Append to Array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related