Autofilter using cell reference

[Eliott]

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]

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?

 

[lukeois]

Good Find

Dave,

Have spent ages trying to work out the Auofilter date issue but one of your ideas worked. I know its a while since this thread but just wanted to say thanks and show you the code that worked.

Sub datetest()
		  
	Dim sdate  As Date
	Dim edate  As Date
 
		 sdate = InputBox("Please enter start date")
		 edate = InputBox("Please enter end date")
 
		 Range("D1") = sdate
		 Range("F1") = edate
 
Range("dates").AutoFilter Field:=1, _
		 Criteria1:=">=" & CDbl(Range("d1").Value), _
		 Operator:=xlAnd, _
		 Criteria2:="<=" & CDbl(Range("f1").Value)
 
End Sub

Regards (And many thanks)

Luke