For loop textbox

[trucha17]

Hi,

this may sound easy but i'm new to vba so i don't know the exact syntax to use...

I want to loop throught all Textbox in a userform and wanna perfom a specific command if they are empty or not...
the boxes are all named TextBox1, TextBox2, ... so I wanna have acces to the value in it and apply a filter with those values

so basically i wanna write the criteria of my filter in a textbox and applythem:

lets say there is 55045 in TextBox1 and 1234 in TextBox2 and all they others are empty

dim i as integer
for i = 1 to 20
TextBox1=TextBox1.value
if textbox1,2,3,... <> "" then
ActiveSheet.Range("$A$1:$W$500").AutoFilter Field:=3, Criteria1:=Array("55045", "1234"), Operator:=xlFilterValues

I also try to put the value in an array but it didn't work out well cause i needed the quotes to apply the filter after...

Thanks for your help!

 

[jproffer]

I wasn't able to test it, but try something like this:

Sub test()
Dim i As Integer
Dim MyArr As String
For i = 1 To 20
'textbox1 = textbox1.Value <--this line commented out as it is not necessary
    If Userform1.Controls("TextBox" & i) <> "" Then
        If MyArr = "" Then
            MyArr = Userform1.Controls("TextBox" & i).Value
        Else
            MyArr = MyArr & ", " & Userform1.Controls("TextBox" & i).Value
        End If
    End If
Next i
ActiveSheet.Range("$A$1:$W$500").AutoFilter Field:=3, Criteria1:=Array(MyArr), Operator:=xlFilterValues
End Sub

 

[trucha17]

First, Thank you for your help!!! But it didn’t work out, it apply the filter, but there is no term anymore, so I assume that the criteria take the whole array one. Like I said, maybe you absolutely need quote between 2 criteria

 

[jproffer]

That could be I suppose. If that's the case, then it would be more like this:

b test()
Dim i As Integer
Dim MyArr As String
For i = 1 To 20
'textbox1 = textbox1.Value <--this line commented out as it is not necessary
    If Userform1.Controls("TextBox" & i) <> "" Then
        If MyArr = "" Then
            MyArr = """" & Userform1.Controls("TextBox" & i).Value & """"
        Else
            MyArr = MyArr & ", """ & Userform1.Controls("TextBox" & i).Value & """"
        End If
    End If
Next i
ActiveSheet.Range("$A$1:$W$500").AutoFilter Field:=3, Criteria1:=Array(MyArr), Operator:=xlFilterValues
End Sub

 

[trucha17]

Finally it wasn’t that (quotes) cause if I only try with 1 value, it apply the filter...
even if I’m new to this, i have a strong felling that it that the array as 1 argument and not separated... Ii wasn’t able to make it work so I just hardcode it... array(textbox1, textbox2,...)
And it work fine except it select all the row to 500(the range of the filter)
Because some textbox are empty but now the problem Is that I cannot count the number of visible row anymore...
i found a formula somewhere but it now gives the blank one too cause of the filter
so I just managed to create a little math formula to do it...
Count = number of visible rows at the beginning - 500 + number of row counted with blank
not super super beautiful code, but it work just fine for me hahaha
i cannot put a specific range cause it’s a daily report I have to do at work each morning and it changes all the time...
So thank you for you help buddy!!!

 

[Fluff]

How about

Private Sub CommandButton1_Click()
   Dim i As Long, j As Long
   Dim Ary(20) As Variant
   For i = 1 To 20
      If Me.Controls("TextBox" & i) <> "" Then
         Ary(j) = Me.Controls("TextBox" & i).Value
         j = j + 1
      End If
   Next i
   ActiveSheet.Range("A1:W1").AutoFilter Field:=3, Criteria1:=Ary, Operator:=xlFilterValues
End Sub

 

[Rick Rothstein]

How about

Private Sub CommandButton1_Click()
   Dim i As Long, j As Long
   [B][COLOR="#FF0000"]Dim Ary(20) As Variant[/COLOR][/B]
   For i = [B][COLOR="#FF0000"]1[/COLOR][/B] To 20
      If Me.Controls("TextBox" & i) <> "" Then
         Ary(j) = Me.Controls("TextBox" & i).Value
         j = j + 1
      End If
   Next i
   ActiveSheet.Range("A1:W1").AutoFilter Field:=3, Criteria1:=Ary, Operator:=xlFilterValues
End Sub

It probably does not matter (I was too lazy to set up a UserForm to check), but since you are iterating the array starting at element number 1, technically you should declare your array to start at element 1 (as declared above, there is an element 0 which is being ignored)...

Dime Ary(1 To 20) As Variant

 

[Fluff]

If I was populating the array based on the value of i, I'd agree, but it's being populated on the value of j, which starts at 0.

 

[Rick Rothstein]

If I was populating the array based on the value of i, I'd agree, but it's being populated on the value of j, which starts at 0.

{I must learn to read code more carefully}