excel gurus, need help with this code please!

Posted by Kevin on December 10, 2001 8:21 AM

I have a macro that that changes the autofilter criteria automatically:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
On Error Resume Next
If Target.Address = "$D$4" Then
If Range("D4").Value = 0 Then
End
Else
Selection.AutoFilter Field:=1, criteria1:=">=2/1/2001", Operator:=xlAnd, Criteria2:="&LT;=7/1/2002"
End If
End If
End Sub

Right now I just have specific dates inserted into the code. Is there any way to change this code so that instead of having dates in the code there are cell references, and when their values change the autofilter criteria will update???

Thanks in advance,
Kevin

Posted by Tom Morales on December 10, 2001 11:29 AM

Kevin - I would think yes. Try inserting some code like:
Dim crit As String
Dim crit1 As String
crit = Range("D1").Value 'Here's the variable date
crit1 = ">=" & crit
Selection.AutoFilter Field:=1, Criteria1:=crit1, Operator:=xlAnd, Criteria2:="&LT;=7/1/2002"

You could do the same for Criteria2, of course.
Tom

Posted by Kevin on December 10, 2001 12:24 PM

Thanks a million!