MrExcel Publishing
Your One Stop for Excel Tips & Solutions

More Advanced Filter Woes


Posted by Catherine Munro on November 19, 2001 12:17 PM

I have an Advanced Filter run by a macro to find item sales that are up or down by a user-chosen percentage. They have an option button to choose UP or DOWN, linked to C5, and they fill in L5 with chosen percent.

'if option is "UP by %"
If Range("C5").Value = 1 Then

'set formula in criteria row to find items GREATER than percentage...
Range("AD8").Formula = "='% Change'>=$L$5"

'...filter data based on new parameters...
Range("V18:AF5000").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("V7:AF8"), _
CopyToRange:=Range("D18:N18"), Unique:=True

'...and sort descending by % change (highest percent at top)
ActiveSheet.Range("D18:N4000").Sort _
Header:=xlYes, _
Key1:=ActiveSheet.Range("L18"), _
Order1:=xlDescending

'if option is "DOWN by %"
Else
'filter for items LESS than percentage...
Range("AD8").Formula = "='% Change'<=-$L$5"
'...and so on.
End If

The problem is this: If the criteria formula reads "='% Change'>=$L$5", the macro pops up an "Identify Label" dialog box ("There is more than one cell with this label, select the cell with the label to use."). The only other cell with that label is the one in the 'Copy To' range headers, that I apparently can't change without getting 'missing or illegal field name' errors. (I do need this column displayed in the output.) If I put the cell reference to the correct label (AD18) into the dialog box, the filter works the way it is supposed to. Of course, I don't want the user to confront this.

However, if I change the formula to "=AD18>=$L$5", or any absolute/relative variation thereof that does NOT use the column label, the filter does not work properly: it does not filter out items less than the chosen percentage. In some variations, I get no data back whatsoever.

So what solutions are there?

Do I need to put the formula into the criteria row differently? I've already tried many different non-VB solutions on the worksheet, involving IF statements and so on. There's probably an elegant solution I haven't figured out yet, but since I need the macro to run the Filter anyway I've been trying it this way. (Deadlines, you know...)

Or, can I use VB to supply the "Identify Label" information? I can't find any reference in Help.

Or if there's simply a workaround I can use -- PLEASE let me know ASAP....

Thanks, Catherine


Posted by Catherine Munro on November 20, 2001 10:21 AM

Doh!

I found it -- it helps if you use the address for the first row of DATA (AD19) in your formula instead of the column label.