Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with ShowFilter macro
I'm trying to use this ShowFilter UDF written by Tom Ogilvy (see bottom of
post). It says to use... =showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) ....in a cell to show the criteria for Column B. For one thing, I don't understand the cells B2 and B3 business. What is supposed to be in those cells? I would like this function to appear in the cell directly above or below the Autofilter menu cell. How do I change the function if the Autofilter criteria menu is in, cell A3, for example, and I want the criteria (this function) to appear just above, in cell A2? After trying all sorts of things in different cells, the only thing I would get is the #VALUE error. Thanks for your help. Public Function ShowFilter(rng As Range) 'UDF that displays the filter criteria. 'posted by Tom Ogilvy 1/17/02 'To make it respond to a filter change, tie it to the subtotal command. '=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) 'So the above would show the criteria for column B Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function |
#2
|
|||
|
|||
See my response in .misc
-- HTH Bob Phillips "Cheese" wrote in message ... I'm trying to use this ShowFilter UDF written by Tom Ogilvy (see bottom of post). It says to use... =showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) ...in a cell to show the criteria for Column B. For one thing, I don't understand the cells B2 and B3 business. What is supposed to be in those cells? I would like this function to appear in the cell directly above or below the Autofilter menu cell. How do I change the function if the Autofilter criteria menu is in, cell A3, for example, and I want the criteria (this function) to appear just above, in cell A2? After trying all sorts of things in different cells, the only thing I would get is the #VALUE error. Thanks for your help. Public Function ShowFilter(rng As Range) 'UDF that displays the filter criteria. 'posted by Tom Ogilvy 1/17/02 'To make it respond to a filter change, tie it to the subtotal command. '=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) 'So the above would show the criteria for column B Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro with F9 | Excel Discussion (Misc queries) | |||
Help with macro looping and color query function | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
Macro and If Statement | Excel Discussion (Misc queries) |