Building Array in VBA with For Loop 1

[RSchnable]

Hello everyone,

I have built a form in VBA for an Excel spreadsheet to search. I have 4 checkboxes, and if they are checked I want to filter on those names in a specific field. I have gotten this far. I can get one filter to work properly, but when I try to filter on 2 or more names, the array doesn't build properly and it errors out.

Here is what I have.

    arrFilter = VBA.Array(chkRon, chkDave, chkTodd, chkWayne)
    arrField = VBA.Array("Ron", "Dave", "Todd", "Wayne")
    
    For i = 0 To 3
    If arrFilter(i) = True Then
    arrFinal = arrFinal + arrField(i)
    End If
    Next i
       
    .AutoFilter Field:=2, Criteria1:=arrFinal

I have tried modifying the code to no avail. I've tried using the ReDim statement. I can't find anything that works. Thanks in advance for the help.

 

[Andrzejek]

Some questions:

[red]
Dim arrFilter As ???
Dim arrField As ???[/red]

arrFilter = VBA.Array(chkRon, chkDave, chkTodd, chkWayne)
arrField = VBA.Array("Ron", "Dave", "Todd", "Wayne")
    
For i = 0 To 3
    [red]Debug.Print arrFilter(i)[/red][green]   '???[/green]
    If arrFilter(i) = True Then
        arrFinal = arrFinal + arrField(i)
    End If
Next i
       
.AutoFilter Field:=2, Criteria1:=arrFinal

Have fun.

---- Andy

 

[RSchnable]

Thanks for responding. All arrays are dimmed as Variants. I hadn't included Debug in my code up to this point.

 

[SkipVought]

You have not provided enough info.

What error?

On what statement?

EXACTLY what is in the variable, arrFinal in the last statement posted.

What does that statement refer to?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RSchnable]

I apologize. The first array, arrFilter, is the collection of the checkboxes on my form. My goal is to loop through that array, checking if each one is true or not. For each one that is true, I want to add the corresponding value from the second array, arrField, to my final array, arrFinal. I want the arrFinal to be an array of all true vales from arrField, depending on whether or not the corresponding checkbox in arrFilter is true.

So if chkRon and chkDave are both true, I want my final arrFinal array to be VBA.Array("Ron", "Dave"), so that my autofilter will include both.

The problem I run into is when more than one box is checked, I am not building my array correctly, so instead of VBA.Array("Ron", "Dave"), my autofilter is merely looking for the value RonDave, which does not exist.

 

[SkipVought]

This is not how AutoFilter works.

If you check more than ONE box, the affFinal will contain your values strung together, like RonDave. Trouble is, you have no value in your table that is equal to RonDave, do you?

You have to build your procedure to work with the feature of Excel!!!

Do you expect that 3 or more boxes could be checked?

If so, how will THAT work with the feature of Excel!!!


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

You might look at Advanced Filter instead, if you have more than TWO criteria.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RSchnable]

Well I guess I'll take a look at that. Any number of checkboxes, from 1 to 4, can be checked. When I used Record Macro, and checked 3 boxes, the autofilter recorded with the Array("Ron", "Dave", "Todd") method, so that's the reason I tried to build off of that.

 

[SkipVought]

Do you understand the LIMITS of AutoFilter?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RSchnable]

I guess I don't.

 

[PHV]

You wanted this ?

j = 0
For i = 0 To 3
  If arrFilter(i) = True Then
    ReDim Preserve arrFinal(j)
    arrFinal(j) = arrField(i)
    j = j + 1
  End If
Next i

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RSchnable]

I was busy while I was responding to your posts, but after looking back, my question really has nothing to do with AutoFilter. I can populate a static array as my Criteria and filter it just fine. My question relates to how I add values, conditionally, to an array.

 

[RSchnable]

I think so PHV, but just as when I tried using ReDim Preserve, I get Runtime Error 13 Type Mismatch error on the ReDim line.

 

[PHV]

Dim arrFinal()
j = 0
For i = 0 To 3
If arrFilter(i) = True Then
ReDim Preserve arrFinal(j)
arrFinal(j) = arrField(i)
j = j + 1
End If
Next i

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RSchnable]

I feel like it's on the right track, but for some reason my filter only includes the last value in the loop. It does not include all checked values. Any ideas?

 

[RSchnable]

Ahhh. Just was missing a line of code somewhere else. Works great now. Thanks PHV!

 

[SkipVought]

Asking the correct question is very important.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}