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

 

[asyamonique]

Hi Again,
So I just captured the image of userform for your information and the label names will be always same...
As you said, I excatly want to count of the duplicate values on listbox column to be set as a caption on the label that has the same number as the value or text in the listbox!
Thanks again

 

[MickG]

Picture not showing!!

Q() So assuming you have a Label for each value in the Listbox. How will you know you are getting the right result on the right Label ???? :- What visibly defines the Relationship between the Listbox values and the Label Names.

 

[asyamonique]

Hi,
I've fixed by creating a sheet and put into the cells given below formula base on main data.
And I've put the below code for every single labels.
So far it works well.
Thanks again.

=COUNTIFS(list!D:D,$C$1,list!$A:$A,$B$1)

'Date1Me.Label263.Caption = ThisWorkbook.Sheets("info").Range("a6")
Me.Label264.Caption = ThisWorkbook.Sheets("info").Range("a7")
Me.Label266.Caption = ThisWorkbook.Sheets("info").Range("a8")
Me.Label265.Caption = ThisWorkbook.Sheets("info").Range("a9")
Me.Label267.Caption = ThisWorkbook.Sheets("info").Range("a10")
Me.Label268.Caption = ThisWorkbook.Sheets("info").Range("a11")
Me.Label270.Caption = ThisWorkbook.Sheets("info").Range("a12")
Me.Label269.Caption = ThisWorkbook.Sheets("info").Range("a13")
Me.Label271.Caption = ThisWorkbook.Sheets("info").Range("a14")
Me.Label272.Caption = ThisWorkbook.Sheets("info").Range("a15")
Me.Label274.Caption = ThisWorkbook.Sheets("info").Range("a16")
Me.Label273.Caption = ThisWorkbook.Sheets("info").Range("a17")