userform with date searching in worksheet

  • Thread starter Thread starter excelnewbie166
  • Start date Start date
E

excelnewbie166

I have a worksheet where one of the columns is a date column. I have a
userform for the user to enter their start date and end date for a
query. I need code that will take the start date and end date and
compare it to what is in this column. If the date in the column falls
between the two dates entered, I need all of the information in the row
to be copied to another worksheet. The dates in the column are not
sorted.

Any suggestions on where to start? I'm a newbie at this so please be
gentle.
..
 
Assuming you have a userform with two text boxes named
tbxStartDate and tbxEndDate, put the following code behind a
command button. Change the ranges marked with << to the
appropriate locations in your workbook.


Private Sub CommandButton1_Click()

Dim DestRng As Range
Dim SearchRng As Range
Dim StartDate As Date
Dim EndDate As Date
Dim Rng As Range

StartDate = CDate(Me.tbxStartDate.Text)
EndDate = CDate(Me.tbxEndDate.Text)

Set DestRng = Worksheets("Sheet2").Range("A1") '<< CHANGE
Set SearchRng = Worksheets("Sheet1").UsedRange.Columns(1) '<<
CHANGE
For Each Rng In SearchRng.Cells
If Rng.Value >= StartDate And Rng.Value <= EndDate Then
Rng.EntireRow.Copy Destination:=DestRng
Set DestRng = DestRng(2, 1)
End If
Next Rng

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"excelnewbie166" <[email protected]>
wrote in message
news:[email protected]...
 
Hi Chip, thanks for the quick reply. It works fine but I
get a "Run Time 13 error Type mismatch" for some reason at
the beginning of the If statement.

I did change the column to (3) as the coolumn I need to
check is the third from the left in the worksheet. Also
the column is being filled in from another worksheet via a
IFBLANK statement. Would either of these make a difference?

Thanks again for your help.
 
Is there invalid data in the column of dates? The only way I
could get a type mismatch error is if one of the date cells
contained an error value. You can use IsDate to ensure that the
cell contains a valid date. For example:

For Each Rng In SearchRng.Cells
If IsDate(Rng) Then
If Rng.Value >= StartDate And Rng.Value <= EndDate
Then
Rng.EntireRow.Copy Destination:=DestRng
Set DestRng = DestRng(2, 1)
End If
End If
Next Rng


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
There are blanks as not all of the data is filled in.
Would this cause the problem? If so, how would I skip
blank values - I'm assuming I would use the IsDate as
outlined below.

Again, thanks for your help and patience.
 
Yes, using the IsDate function as shown in the code will cause
the code to skip over blank values.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top