Macro for Autofilter that filters on a column based on the value of a cell

[jen1220]

I am attempting to filter a column depending on a value in another cell. The value is used to identify the column number in a range. So if the value is 4, then I want to filter column 4.

This is what my table in excel looks like:
xtable image.jpg


In this screen shot, the value in Q3 = 4, so I want column V (which is the 4th column in my range as the range is from S to AC) to filter for the 1s.

So far I have this code which creates the filters and runs the filter. “Subject_PropertyTypeTitles” is the range name of the title row where the filters are created:

Application.Goto Reference:="Subject_PropertyTypeTitles"
Selection.AutoFilter
ActiveSheet.Range("$R$3:$AC$104").AutoFilter Field:=1, Criteria1:="Entry"
ActiveSheet.Range("$R$3:$AC$104").AutoFilter Field:=4, Criteria1:="1"


However, where it says “Autofilter Field:=4”, I want the 4 to be whatever number is in Q3.

Can you please help?

Thanks,

Jen

[pjwhitfield]

Change the 4 to "Cells(3,22).value" (without the speech marks)

[jen1220]

I changed the 4 to cells(3.22).value, but it gave an error. Here is the code now:
Application.Goto Reference:="Subject_PropertyTypeTitles"
Selection.AutoFilter
ActiveSheet.Range("$R$3:$AC$104").AutoFilter Field:=1, Criteria1:="Entry"
ActiveSheet.Range("$R$3:$AC$104").AutoFilter Field:=(3,22).value, Criteria1:="1"

It says, "compile error, syntax error".
Also, can you please explain what 3,22 represents? I am new to VBA code.

Thanks!

[jen1220]

Another option is that I can write an if then statement for every scenario. For instance, If Q3=1, then ActiveSheet.Range("$R$3:$AC$104").AutoFilter Field:=1, Criteria1:="1", else if Q3=2, then ActiveSheet.Range("$R$3:$AC$104").AutoFilter Field:=2, Criteria1:="1"

However, I do not know how to write if then statements in VBA. Please help me come up with a solution!

Jen

[jaslake]

Hi Jen

Change

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.