Find by date range

Hi all,

I am trying to modify another sub of mine so that instead of searching
by one criteria it searches by two date ranges input on the form and
brings back a list of everything between those dates. The part that is
not working is where I choose the criteria, nothing I have tried seems
to work, I tried using >= date1.value and <=date2.value but that wont
work, I will post the full sub below and perhaps somebody could help?
Maybe I should do it a whole differant way instead of trying to modify
what I already had, taking the easy way out never works for
me!...........



Private Sub CommandButton1_Click()

Sheets("sheet1").Select
Dim rng As Range

Set rng = Range("A1:I" & Range("a65536").End(xlUp).Row)
rng.Select

Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.Select

Columns("d:d").AutoFilter Field:=4, _
Criteria1:=Date1.Value, Criteria2:=Date2.Value

Select Case MsgBox("Print?", vbYesNo)

Case vbYes
Sheet1.Activate
rng.Select

Selection.PrintOut Copies:=1, Collate:=True

Selection.AutoFilter
UserForm1.Show
regTrail.Value = ""
regTrail.SetFocus
Exit Sub

Case vbNo
Selection.AutoFilter
UserForm1.Show
regTrail.Value = ""
regTrail.SetFocus

Exit Sub
End Select
Sheets("sheet1").Select

End Sub

Try the following (assuming Date1 is the start date for the date range and
Date2 is the end):
Columns("d:d").AutoFilter Field:=4, _
Criteria1:=">=" & Date1.Value, Criteria2:= "<=" & Date2.Value

Note that the dates in Date1 and Date2 must be actual date values, not text.
If you have a text representation of the date, then use
DateValue(Date1.Value) to convert it to a serial date number.
--
- K Dales


"Duncan" wrote:

> Hi all,
>
> I am trying to modify another sub of mine so that instead of searching
> by one criteria it searches by two date ranges input on the form and
> brings back a list of everything between those dates. The part that is
> not working is where I choose the criteria, nothing I have tried seems
> to work, I tried using >= date1.value and <=date2.value but that wont
> work, I will post the full sub below and perhaps somebody could help?
> Maybe I should do it a whole differant way instead of trying to modify
> what I already had, taking the easy way out never works for
> me!...........
>
>
>
> Private Sub CommandButton1_Click()
>
> Sheets("sheet1").Select
> Dim rng As Range
>
> Set rng = Range("A1:I" & Range("a65536").End(xlUp).Row)
> rng.Select
>
> Range("A1").End(xlDown).Offset(1, 0).Select
> ActiveCell.Select
>
> Columns("d:d").AutoFilter Field:=4, _
> Criteria1:=Date1.Value, Criteria2:=Date2.Value
>
> Select Case MsgBox("Print?", vbYesNo)
>
> Case vbYes
> Sheet1.Activate
> rng.Select
>
> Selection.PrintOut Copies:=1, Collate:=True
>
> Selection.AutoFilter
> UserForm1.Show
> regTrail.Value = ""
> regTrail.SetFocus
> Exit Sub
>
> Case vbNo
> Selection.AutoFilter
> UserForm1.Show
> regTrail.Value = ""
> regTrail.SetFocus
>
> Exit Sub
> End Select
> Sheets("sheet1").Select
>
> End Sub
>
>