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

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