Why isn't this macro giving me proper results?

[Girish]

Good Morning,

Can someone please look in and spot the problem. Basixcally what I'm trying
to do here is to filter a range of dates.

But what actually happens when I run this code is that the Excel filters for
"From1" and "To1" literally (as text/string) and not for values stored in
these range variables.

Solution Please.....

Sub Macro3()
Dim From1, To1 As Range
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
Set From1 = Worksheets("Individual").Range("E45")
Set To1 = Worksheets("Individual").Range("K45")
Sheets("Quality Check Log").Select
Range("A3").Select
Selection.AutoFilter Field:=1, Criteria1:=">=From1", Operator:=xlAnd,
Criteria2:="<=To1"
End Sub


--
Thanks,
Girish
Bangalore, India

[Tushar Mehta]

In article <F65A7EF3-E23F-4E35-93D4-8598DBFE3A23@microsoft.com>,
Girish@discussions.microsoft.com says...
> Good Morning,
>
> Can someone please look in and spot the problem. Basixcally what I'm trying
> to do here is to filter a range of dates.
>
> But what actually happens when I run this code is that the Excel filters for
> "From1" and "To1" literally (as text/string) and not for values stored in
> these range variables.
>
> Solution Please.....
>
> Sub Macro3()
> Dim From1, To1 As Range
> With Application
> .Calculation = xlManual
> .MaxChange = 0.001
> End With
> Set From1 = Worksheets("Individual").Range("E45")
> Set To1 = Worksheets("Individual").Range("K45")
> Sheets("Quality Check Log").Select
> Range("A3").Select
> Selection.AutoFilter Field:=1, Criteria1:=">=From1", Operator:=xlAnd,
> Criteria2:="<=To1"
> End Sub
>
>
>
Untested suggestion.
Selection.AutoFilter Field:=1, Criteria1:=">=" & From1.Value, _
Operator:=xlAnd, Criteria2:="<=" & To1.Value

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

[Dave Peterson]

And just to add to Tushar's response--sometimes autofilter has trouble with
dates:

Selection.AutoFilter Field:=1, Criteria1:=">=" & clng(From1.Value), _
Operator:=xlAnd, Criteria2:="<=" & clng(To1.Value)



Girish wrote:
>
> Good Morning,
>
> Can someone please look in and spot the problem. Basixcally what I'm trying
> to do here is to filter a range of dates.
>
> But what actually happens when I run this code is that the Excel filters for
> "From1" and "To1" literally (as text/string) and not for values stored in
> these range variables.
>
> Solution Please.....
>
> Sub Macro3()
> Dim From1, To1 As Range
> With Application
> .Calculation = xlManual
> .MaxChange = 0.001
> End With
> Set From1 = Worksheets("Individual").Range("E45")
> Set To1 = Worksheets("Individual").Range("K45")
> Sheets("Quality Check Log").Select
> Range("A3").Select
> Selection.AutoFilter Field:=1, Criteria1:=">=From1", Operator:=xlAnd,
> Criteria2:="<=To1"
> End Sub
>
> --
> Thanks,
> Girish
> Bangalore, India

--

Dave Peterson

[Girish]

Thanks a ton guys, both your suggestions are working. great.
--
Girish Bhatta V.
Bangalore, India


"Tushar Mehta" wrote:

> In article <F65A7EF3-E23F-4E35-93D4-8598DBFE3A23@microsoft.com>,
> Girish@discussions.microsoft.com says...
> > Good Morning,
> >
> > Can someone please look in and spot the problem. Basixcally what I'm trying
> > to do here is to filter a range of dates.
> >
> > But what actually happens when I run this code is that the Excel filters for
> > "From1" and "To1" literally (as text/string) and not for values stored in
> > these range variables.
> >
> > Solution Please.....
> >
> > Sub Macro3()
> > Dim From1, To1 As Range
> > With Application
> > .Calculation = xlManual
> > .MaxChange = 0.001
> > End With
> > Set From1 = Worksheets("Individual").Range("E45")
> > Set To1 = Worksheets("Individual").Range("K45")
> > Sheets("Quality Check Log").Select
> > Range("A3").Select
> > Selection.AutoFilter Field:=1, Criteria1:=">=From1", Operator:=xlAnd,
> > Criteria2:="<=To1"
> > End Sub
> >
> >
> >
> Untested suggestion.
> Selection.AutoFilter Field:=1, Criteria1:=">=" & From1.Value, _
> Operator:=xlAnd, Criteria2:="<=" & To1.Value
>
> --
> Regards,
>
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
>