Create dynamically sized array to store row counter

Question

The function below finds the first result.

There could be duplicate rows with matching values that meet my if statements. How would I create an array to store the row numbers the search function found so that I can process the data later.

How would I create the array size based on the number of results found in the for loop?

I am assuming that the for loop counter will have some sort of role to play in this. Let's say the for loop found 2 matches in row numbers 56 and 98 matching my if statements:

array_example(counter, 0) = 56
array_example(counter, 0) = 98

The stored values would be:

array_example(1, 0) = 56
array_example(2, 0) = 98

Private Sub Complex_Search(col1, cval1, col2, cval2)

'MsgBox (col1 & " " & col2)
'MsgBox (cval1 & " " & cval2)

Dim i
Dim lRow
Dim Counter

    lRow = Cells(Rows.Count, 1).End(xlUp).row

    Counter = 0

    With Sheets("Office Spaces")

        For i = 2 To lRow

            If LCase(.Cells(i, col1).Value) = LCase(cval1) And LCase(.Cells(i, col2).Value) = LCase(cval2) Then

                row = i

                Counter = Counter + 1

            End If

        Next i

    End With

    If row = "" Then

        MsgBox ("Search complete. 0 results found")

    Else
        GetRowData (row)

        UserForm1.resmax.Value = 1
    End If

End Sub

Jchang43 · Accepted Answer · 2018-11-06 19:49:56Z

It's worth noting that you haven't even initialized row, and have just let vba implicitly declare it as a variant type for you. To avoid common problems that arise from typos, include Option Explicit at the top of your code and Dim every variable with the type beside it. For example: Dim i as long. Dim i will work, but it will declare it as a variant type

To initialize an array in VBA you use Dim row() as variant. From there you can re-dimension its size using Redim row(LboundX to UboundX) but this will reset all the stored values to zero. To get around this use Redim Preserve row(LBoundX to UBound X).

If you wish to use a 2D array, add a comma and then put the bounds for the next dimension Redim Preserve row(LBoundX to UBound X, LboundY to UBoundY)

At the top of your code I would include

 Dim row() as Variant
 Redim Preserve row(1 to 1)

Then within the loop I would change row = i to

row(Ubound(row)) = i 
Redim Preserve row(1 to Ubound(row) +1)

Now that you have an array though, the check you do below will no longer work and will likely throw an error because you there's no specified index. Instead I suggest changing it from If row = "" Then to If Counter = 0 Then.

I'm not sure what the intention is with GetRowData(row) but you can just access each row number via row(i). It is worth noting however, that the row array will have Counter +1 number of items, but the last one will be blank. You could get around this by adding in an if statement within the already existing one that would look something like this:

If Counter = 0 Then
    row(1) = i
Else
    ReDim Preserve row(1 To UBound(row) + 1)
    row(UBound(row)) = i
End If

Counter = Counter + 1

By implementing this change, row should have exactly Counter number of items that all have a non-empty value

I don't really suggest making a column array because it becomes more cumbersome to change the array size. Redim preserve or not only allows you to change the last dimension in the array, so to change the number of rows you would have to set the array equal to the transpose of itself, do the redim and then set it to the transpose of itself again. It's just needlessly messy. If you're pasting it to a sheet and need it to be in a column you could just transpose it at the end instead.

Collectives™ on Stack Overflow

Create dynamically sized array to store row counter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related