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

Find by date range
 

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