How to match data from worksheet and show it in ListBox?

[z-eighty2]

Hello EveryOne!

I have a userform with two combo boxes and a ListBox.

The userform brings the data from “Sheet1” and display it in userform ListBox1 by matching ComboBox1 and Combox2 requirments.

ComboBox1 is working ok. ComboBox1 match its data from Coloumn 1 of Sheet1.

The data for ComboBox2 to match is in column 3 and 4 of Sheet1.

For example.

In the Userform if I select year “2014” and item “ Laptop”. It just display data of “Line 2”and “Line 5” (It just look at column 3 of Sheet1)

But It should also show the data of “Line 3” and “Line 4” of Sheet1 as well because Item “Laptop” is also there .It should also look at column 4 of Sheet 1 as well.

So how from coding I can edit the following line so that ComboBox2 should look both in Field 3 and 4 of Sheet 1.

.Range("A1:D" & LastRow).AutoFilter Field:=3, Criteria1:=Me.ComboBox2.Value


I have attched userform as well.

I would be very thankful for your help guys.

Best Regards
z-eighty2

Please Login or Register  to view this content.

[Naveed Raza]

Hi replace your combobox 2 Code with below one

i just make slight changes

Please Login or Register  to view this content.

[z-eighty2]

Hi,

Thanks "Naveed Raza" for your help.Your code is working perfect as I want.

One more little help please how the following code line will be change if I have 2 more columns in
Sheet1. (e.g Sales in Region 3 and Sales in Region 4). How the code will look if I add two more columns to filter?

For j = 1 To 2

.Range("A1:D" & LastRow).AutoFilter Field:=j + 2, Criteria1:=Me.ComboBox2.Value

Thanks,

Kind Regards
z-eighty2

[Naveed Raza]

Just replace 2 with that number suppose if u add 2 more columns then change 2 to 4

Please Login or Register  to view this content.

[z-eighty2]

Hi Naveed Raza,

I tried to change the code from " 1 to 4 " but it does not work and give error?
Any idea?

Regards
z-eighty2

[Naveed Raza]

if you have change 2 to 4 then you must have to change the next two line as well to filter that value

this is our previous code for 2

Please Login or Register  to view this content.

now in below code i change 2 to 4

Please Login or Register  to view this content.

so if your changing the value then at the same time u have to change the column name as well