PDA

View Full Version : [SOLVED] Filter Sheet by two columns on two text box values



adamsm
03-06-2011, 03:36 AM
Hi anyone,

The following code filters column B by value on textbox1.
How could the code be changed so that it does filter the column B by value on textbox1 and also it filters the column C by value on text box2?

Any help on this would be kindly appreciated.

Sub Filter()
If UCase(Sheets("MySheet").Range("B5").Value) <> "ALL" Then
Range("A4").AutoFilter Field:=2, Criteria1:=Sheets("MySheet").TextBox1.Text
Else
If Sheets("MySheet").AutoFilterMode Then
Sheets("MySheet").Range("A4").AutoFilter
End If
End If
End Sub

p45cal
03-06-2011, 06:03 AM
a stab in the dark, add this line straight after the similar existing line:

Range("A4").AutoFilter Field:=3, Criteria1:=Sheets("MySheet").TextBox2.Text

shrivallabha
03-06-2011, 07:51 AM
Probably it should be criteria2 after criteria1.

adamsm
03-06-2011, 08:55 AM
Thanks for the help p45cal & shrivallabha, But your suggestion does not seem to work on me.

Suppose If I write "Adamsm" in text box 1 and "Cash" in text box 2; the sheet does not filter the rows containing "Adamsm" from column "B" and "Cash" from column "C".

Instead the sheet shows no data rows.

Any help to modify the code accordingly would be kindly appreciated.

Thanks in advance.

I've attached the workbook for your reference.

p45cal
03-06-2011, 10:49 AM
Well it worked as expected with these two lines:

Range("A4").AutoFilter Field:=2, Criteria1:=.TextBox1.Text
Range("A4").AutoFilter Field:=3, Criteria1:=.TextBox2.Text

However, if any textbox is empty you will filter for empty cells. Try this adaptiation:

Sub Filter()
With Sheets("MyData")
If UCase(.Range("B5").Value) <> "ALL" Then
strUser = .TextBox1.Text
strPayType = .TextBox2.Text
If strUser = "" Then
.Range("A4").AutoFilter Field:=2
Else
.Range("A4").AutoFilter Field:=2, Criteria1:=strUser
End If
If strPayType = "" Then
.Range("A4").AutoFilter Field:=3
Else
.Range("A4").AutoFilter Field:=3, Criteria1:=strPayType
End If
Else
If Sheets("Mydata").AutoFilterMode Then
Sheets("Mydata").Range("A4").AutoFilter
End If
End If
End With
End Sub

(Use Criteria1 for both lines.)

shrivallabha
03-06-2011, 10:59 AM
Yup, p45cal is correct....

Sub Filter()
If UCase(Sheets("Mydata").Range("B5").Value) <> "ALL" Then
Range("A4").AutoFilter Field:=2, Criteria1:=Sheets("Mydata").TextBox1.Text
Range("A4").AutoFilter Field:=3, Criteria1:=Sheets("Mydata").TextBox2.Text
Else
If Sheets("Mydata").AutoFilterMode Then
Sheets("Mydata").Range("A4").AutoFilter
End If
End If
End Sub

The criteria2 part is used when multiple criteria is set on the same data. Apologies for confusion caused.

adamsm
03-07-2011, 02:49 AM
Thanks for the help.

How should I change the code if my sheets column headers are on row 16. and the the column where filter is applied starts from column C? My data rows start from row 17.

Here is my try. But this is giving me debug messages. Also instead of text boxes I'm using combo boxes.

The column with the header "User" is on column "H". The column with the "PayType" is on column "M".


Sub Filter()
If UCase(Sheets("MyData").Range("D17").Value) <> "ALL" Then
Range("C16").AutoFilter Field:=6, Criteria1:=Sheets("MyData").cboUser.List
Range("C16").AutoFilter Field:=7, Criteria1:=Sheets("MyData").cboPayType.List
Else
If Sheets("MyData").AutoFilterMode Then
Sheets("MyData").Range("C16").AutoFilter
End If
End If
End Sub

Any help on this would be kindly appreciated.

Thanks in advance.

p45cal
03-07-2011, 03:05 AM
You can attach a cut-down and with-sensitive-data-removed file with things arranged as you will eventually like them to be - it's hard work manually reproducing what you describe, especially with moving goal posts.

adamsm
03-07-2011, 08:33 AM
Thanks for the reply.

Attached please find the workbook of which has the issues I have mentioned in the previous post.

Any help on this would be kindly appreciated.

Thanks in advance.

p45cal
03-07-2011, 06:12 PM
see attached

adamsm
03-07-2011, 08:17 PM
Thanks for the help p45cal. I do really appreciate your help.