Adding variable string to array and then filtering on that.

[RockandGrohl]

Hello.

I'd like to add an indeterminate amount of strings to an array, and then use that array as filter criteria on another sheet.

In order to find what to add to the array, I would be looking at the left(6) of column 1, and then the string on column 29 needs to be added.

Here's an example:

Column 1	Column 29	Added?
DONKEY	A1	Y
DONKEY-F	A3	Y
DONKEY-F	A4	Y
DONKEY-F	A6	Y
BADGER	A5	N
BADGER-F	A2	N

So if my current row was on DONKEY, I would see that left(6) would result in finding A1, A3, A4 and A6. So I want to add them to an array, then autofilter a range ("A1:BD" & LastrowDF) on field 56 with the CriteriaArray("A1", "A3" etc)

Is this possible? Can you add things to an array piece by piece? Cheers.

 

[Fluff]

What line did you get the error on?

 

[RockandGrohl]

What line did you get the error on?

If I highlight RegAr(q), when I add the first item to Array (A1), it works. When I do q = q + 1, and I highlight RegAr(q) again, it says < subscript out of range >

So the error message actually comes later when I go to call the values stored in the array, but I can see that doing q = q + 1 to expand the size of the array is what's causing the issue.

Thanks.

 

[Fluff]

Do you get an actual error, or is that just from hovering the cursor over the variable.

 

[RockandGrohl]

Do you get an actual error, or is that just from hovering the cursor over the variable.

Hi hi, yeah later on if I try to do something like this

        For x = LBound(RegAr) To UBound(RegAr)
            Debug.Print RegAr(q)
            MsgBox RegAr(q)
        Next x

Then it says subscript out of range. Cheers

 

[Fluff]

That should be an x not a q

 

[RockandGrohl]

That should be an x not a q

If I paste the whole module here

Sub test()

Do Until Cells(ActiveCell.Row, "A").Value = ""

tref = Left(Cells(ActiveCell.Row, "A").Value, 6)
LastrowPPL = Cells(Rows.Count, "A").End(xlUp).Row

       Dim RegAr() As Variant
        Dim cell As Range, rng As Range
        Dim q As Long
        Set rng = Range("AC3:AC" & LastrowPPL)

        On Error Resume Next
        ActiveSheet.ShowAllData
        On Error GoTo 0


        Range("A2:AG" & LastrowPPL).AutoFilter field:=1, Criteria1:=tref & "*"
        q = 1
        For Each cell In rng.SpecialCells(xlCellTypeVisible)
            ReDim Preserve RegAr(q)
            RegAr(q) = cell.Value
            q = q + 1
        Next cell


        For q = LBound(RegAr) To UBound(RegAr)
            Debug.Print RegAr(q)
            MsgBox RegAr(q)
        Next q

ActiveCell.Offset(1, 0).Activate
Loop

End Sub

What this should be doing is on the first row it sees "N05C18" in line 3 of column A, which is then loaded as tref and filtered with a wildcard, to show me 5 additional lines. So I want to load each of the corresponding region strings in column AC into an array.

When It gets to RegAr(q) = cell.value for the first time, I execute the line and it correctly says "A1" when I highlight it "RegAr"

Then, on the next line when I execute q=q+1, if I highlight RegAr again, it says subscript out of range error.

On the final part of the code which I'm doing to check all strings are loaded into the array properly, when msgbox RegAr(q) loads, the msgbox is blank. By looping through q, it then shows me all the different regions.

Range("A1:BD30594").AutoFilter field:=18, Criteria1:=Array(RegAr)

If I then go onto the Output sheet (which is what I want to filter by the array) and filter field 18 by "RegAry", it only filters by the last region in the array which is "A4"

If I change the code to say "RegAry(q)" then I get subscript out of range again.

 

[Fluff]

It does not filter correctly, because you have not coded it correctly.

 

[RockandGrohl]

It does not filter correctly, because you have not coded it correctly.

Story of my life :')