Error on filtering data by two combobox!

[asyamonique]

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

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

 

[MickG]

You need to fill it with an array, something like this:-

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

 

[asyamonique]

Mick thank you for your help.

 

[MickG]

You're welcome

 

[asyamonique]

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.

 

[MickG]

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.

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

 

[asyamonique]

	LIST COLUMN 1
	6
	6
	10
	10
	12
	14
	14
	14
	22
6	2
8
10	2
12	1
14	3
16
18
20
22	1
24

<tbody>
</tbody>

Mick, actually thats the what excatly I wanted.
Its like

Me.Label11.Caption = listcolumn countif "6"

 

[MickG]

I'm sorry, but I don't understand that, please provide a clearer explanation of what you want !!!

 

[asyamonique]

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.

 

[MickG]

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.