Error on filtering data by two combobox!

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,286
Office Version
  1. 2013
Platform
  1. Windows
Code:
Private Sub CommandButton3_Click()

Dim r As Long
    If ComboBox1.Value <> -1 And ComboBox2.Value <> -1 Then
        ListBox1.Clear
        With Worksheets("list").Range("A1:A30000")
            For r = 1 To .Rows.Count
                If ComboBox1.Value = .Cells(r, 1).Value And ComboBox2.Value = .Cells(r, 2).Value Then
                    ListBox1.AddItem .Cells(r, 1).Value
                    ListBox1.list(ListBox1.ListCount - 1, 1) = .Cells(r, 2).Value
                    ListBox1.list(ListBox1.ListCount - 1, 2) = .Cells(r, 3).Value
                    ListBox1.list(ListBox1.ListCount - 1, 3) = .Cells(r, 4).Value
                    ListBox1.list(ListBox1.ListCount - 1, 4) = .Cells(r, 5).Value
                    ListBox1.list(ListBox1.ListCount - 1, 5) = .Cells(r, 6).Value
                    ListBox1.list(ListBox1.ListCount - 1, 6) = .Cells(r, 7).Value
                    ListBox1.list(ListBox1.ListCount - 1, 7) = .Cells(r, 8).Value
                    ListBox1.list(ListBox1.ListCount - 1, 8) = .Cells(r, 9).Value
                    ListBox1.list(ListBox1.ListCount - 1, 9) = .Cells(r, 10).Value
                    ListBox1.list(ListBox1.ListCount - 1, 10) = .Cells(r, 11).Value
                    ListBox1.list(ListBox1.ListCount - 1, 11) = .Cells(r, 12).Value
                    ListBox1.list(ListBox1.ListCount - 1, 12) = .Cells(r, 13).Value
                    ListBox1.list(ListBox1.ListCount - 1, 13) = .Cells(r, 14).Value
                    ListBox1.list(ListBox1.ListCount - 1, 14) = .Cells(r, 15).Value
                    ListBox1.list(ListBox1.ListCount - 1, 15) = .Cells(r, 16).Value
                    ListBox1.list(ListBox1.ListCount - 1, 16) = .Cells(r, 17).Value
                    ListBox1.list(ListBox1.ListCount - 1, 17) = .Cells(r, 18).Value
                    ListBox1.list(ListBox1.ListCount - 1, 18) = .Cells(r, 19).Value
                    ListBox1.list(ListBox1.ListCount - 1, 19) = .Cells(r, 20).Value
                    ListBox1.list(ListBox1.ListCount - 1, 20) = .Cells(r, 21).Value
                    ListBox1.list(ListBox1.ListCount - 1, 21) = .Cells(r, 22).Value
                    ListBox1.list(ListBox1.ListCount - 1, 22) = .Cells(r, 23).Value
                    ListBox1.list(ListBox1.ListCount - 1, 23) = .Cells(r, 24).Value
                    ListBox1.list(ListBox1.ListCount - 1, 24) = .Cells(r, 25).Value
                    ListBox1.list(ListBox1.ListCount - 1, 25) = .Cells(r, 26).Value
                    ListBox1.list(ListBox1.ListCount - 1, 26) = .Cells(r, 27).Value
                    ListBox1.list(ListBox1.ListCount - 1, 27) = .Cells(r, 28).Value
                    ListBox1.list(ListBox1.ListCount - 1, 28) = .Cells(r, 29).Value
                    ListBox1.list(ListBox1.ListCount - 1, 29) = .Cells(r, 30).Value
                    ListBox1.list(ListBox1.ListCount - 1, 30) = .Cells(r, 31).Value
                    ListBox1.list(ListBox1.ListCount - 1, 31) = .Cells(r, 32).Value
                    ListBox1.list(ListBox1.ListCount - 1, 32) = .Cells(r, 33).Value
                    ListBox1.list(ListBox1.ListCount - 1, 33) = .Cells(r, 34).Value
                    ListBox1.list(ListBox1.ListCount - 1, 34) = .Cells(r, 35).Value
                    ListBox1.list(ListBox1.ListCount - 1, 35) = .Cells(r, 36).Value
                    
                End If
            Next r
        End With
With ListBox1
.ColumnCount = 35
.ColumnWidths = "0;0;150;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42"
End With
End If
End Sub


Good Day,
Could someone help me on that given code above?
Its not running after
Code:
ListBox1.list(ListBox1.ListCount - 1, 10) = .Cells(r, 11).Value
I've changed column count to 35 from ListBox properties but still giving error....What would be the reason?
Many thanks
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
You need to fill it with an array, something like this:-
Code:
Dim r As Long, ac As Long, c As Long
  If ComboBox1.Value <> -1 And ComboBox2.Value <> -1 Then
        ListBox1.Clear
        With Worksheets("list").Range("A1:A30")
           ReDim Ray(1 To .Count, 1 To 35)
            For r = 1 To .Rows.Count
               With ListBox1
                    .ColumnCount = 35
                    .ColumnWidths = "0;0;150;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42"
                End With
                If ComboBox1.Value = .Cells(r, 1).Value And ComboBox2.Value = .Cells(r, 2).Value Then
                   c = c + 1
                   For ac = 1 To 35
                        Ray(c, ac) = .Cells(r, ac)
                   Next ac
                End If
            Next r
        End With
End If
ListBox1.List = Ray
 
Upvote 0
Hi MickG,
Is it possible to count unique values column by column.
Simply would like to add related labels below the listbox and when run the code those labels will caption with the numbers.
Sample: If listbox column1 has 5 apples label1 will show 5
Thanks for your help again.
 
Upvote 0
Perhaps something like this:-
NB:- This code runs on Opening the Userform
I assume you have 35 "Labels" ( 1 to 35) on the Userform, for 35 columns.

Code:
Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Set[/COLOR] dic = CreateObject("scripting.dictionary")
dic.CompareMode = vbTextCompare
[COLOR="Navy"]With[/COLOR] Worksheets("list")
    [COLOR="Navy"]For[/COLOR] Ac = 1 To 35
        [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Cells(1, Ac), .Cells(Rows.Count, Ac).End(xlUp))
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
                dic(Dn.Value) = dic.Count
        [COLOR="Navy"]Next[/COLOR] Dn
        UserForm2.Controls("Label" & Ac).Caption = dic.Count
        dic.RemoveAll
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
[TABLE="width: 141"]
<tbody>[TR]
[TD][/TD]
[TD]LIST COLUMN 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Mick, actually thats the what excatly I wanted.
Its like
Code:
Me.Label11.Caption = listcolumn countif "6"
 
Last edited:
Upvote 0
I'm sorry, but I don't understand that, please provide a clearer explanation of what you want !!!
 
Upvote 0
OK,
Listbox populates the datas on userform.Monthly and daily by your first code!
Below that listbox I've added10 labels which named 6 to 24(times)
So under every columns I've put another labels which are blank with no data.
The one I've given sample which named label"6" has 2 on listbox and blank label has to fill up with the amount of 2.
The project is monthly schedule columns are the dates.So under every column there are 10 blank labels which needed to populate with the amount of times.
Hope its clear.
Many thanks.
 
Last edited:
Upvote 0
It would appear that from your data that the first column in the listbox is shown in post#7 as "LISTCOLUMN".
Now from Post#9 you are saying that underneath that first Listbox column in the Userform are 10 (Evenly) numbered labels, named "Label6" to Label24".

Those label numbers also appear to have some of the same values as in the Listbox columns data.

You appear to want the count of the duplicate values in that listbox column to be set as a caption on the label that has the same number as the value in the listbox column. so for example label6.caption would read "Label6,2"

So if all the labels names relate to the listbox values, what are the label names in the other columns ????
and can you be sure those Labels will always be the right labels for the listbox column values.

That would also mean there are no duplicates values between different columns, in order to have unique Label Names.
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top