Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofilter using cell reference
Hi, would someone please help me with this trouble?
E3 (Input cell) is formatted h:mm I have a macro to detect what's in E3 and autofilter a specific Field to match (=) what's in E3. Although the time format is the same for both E3 and column being filtered, it appears that Excel cannot find a match. I've attempted both of the following but same result...no match. What am I doing wrong? MACRO # 1: Sub check_overlaps() ' ' check_overlaps Macro ' Macro recorded 4/22/2008 by espencer ' Range("A10:T10").AutoFilter Field:=12, Criteria1:=Range("E3").Value ' End Sub MACRO # 2: Sub check_overlaps() ' ' check_overlaps Macro ' Macro recorded 4/22/2008 by espencer ' Range("A10:T10").AutoFilter Field:=12, Criteria1:="=" & Range("E3").Value, Operator:=xlAnd ' End Sub |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofilter using cell reference
Working with autofilter and dates can be a pain.
Maybe the same thing is true with times??? I'd try these until one (I hope) would work: Criteria1:=cdbl(Range("E3").Value) 'match the format of the column being filtered Criteria1:=format(Range("E3").Value, "hh:mm:ss") Criteria1:="=" & Range("E3").Value, _ Operator:=xlAnd, "<=" & Range("E3").Value Criteria1:="=" & cdbl(Range("E3").Value), _ Operator:=xlAnd, "<=" & cdbl(Range("E3").Value) 'match the format of the column being filtered Criteria1:="=" & format(Range("E3").Value, "hh:mm:ss"), _ Operator:=xlAnd, "<=" & format(Range("E3").Value, "hh:mm:ss") I didn't test any of these. Please post back with your results? Eliott wrote: Hi, would someone please help me with this trouble? E3 (Input cell) is formatted h:mm I have a macro to detect what's in E3 and autofilter a specific Field to match (=) what's in E3. Although the time format is the same for both E3 and column being filtered, it appears that Excel cannot find a match. I've attempted both of the following but same result...no match. What am I doing wrong? MACRO # 1: Sub check_overlaps() ' ' check_overlaps Macro ' Macro recorded 4/22/2008 by espencer ' Range("A10:T10").AutoFilter Field:=12, Criteria1:=Range("E3").Value ' End Sub MACRO # 2: Sub check_overlaps() ' ' check_overlaps Macro ' Macro recorded 4/22/2008 by espencer ' Range("A10:T10").AutoFilter Field:=12, Criteria1:="=" & Range("E3").Value, Operator:=xlAnd ' End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing sheet reference to cell reference | Excel Worksheet Functions | |||
Reference to cells using autofilter problem | Excel Worksheet Functions | |||
absolute cell reference A spreadsheet cell reference that does no | Excel Discussion (Misc queries) | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions |