userform with date searching in worksheet

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.
..
 
C

Chip Pearson

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]...
 
E

excelnewbie166

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.
 
C

Chip Pearson

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
 
E

excelnewbie166

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.
-----Original Message-----
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



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.
-----Original Message-----
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
Click to expand...
("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" <excelnewbie166.xmb5y@excelforum- nospam.com>
wrote in message

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
Click to expand...
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
Click to expand...
this
so
please be
gentle.
.


------------------------------------------------
[/url]
~~View and post usenet messages directly from
http://www.ExcelForum.com/



.
Click to expand...
Click to expand...


.
Click to expand...
 
C

Chip Pearson

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


excelnewbie166 said:
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.
-----Original Message-----
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



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.

-----Original Message-----
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" <excelnewbie166.xmb5y@excelforum-
nospam.com>
wrote in message

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.
.


------------------------------------------------
[/url]
~~View and post usenet messages directly from
http://www.ExcelForum.com/



.
Click to expand...


.
Click to expand...
Click to expand...
 
Top