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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/



.

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



"excelnewbie166" wrote in
message ...
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.
.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.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.

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



"excelnewbie166"
wrote in
message ...
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.
.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/



.



.

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" wrote in
message ...
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



"excelnewbie166"
wrote in
message ...
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.
.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/



.



.