Querying on Data Ranges

C

Celeste

I'm looking for code that will prompt the user to enter a 'date range'
(for example: 2004/01/01-2004/01/28). Using this date range, search an entire
spreadsheet (which will contain many columns of dates) and find each
instance of the dates within the input range. Once all dates are
found, copy and paste the entire
row(s)(including column headings) onto another worksheet within the
same workbook.

The result will be a new worksheet just containing all the rows which
contain dates within the specified date range.

Thanks
 
G

Greg Wilson

Celeste,

Q1:
You say that the spreadsheet has "many columns of dates"
and that, when the dates are found, to copy and paste
the "entire row(s)" onto another worksheet. This seems to
imply a conflict: For example, let's say that Column A
and Column D both contain date values. What if Cell A100
has a date falling within the specified range and Cell
D100 has a date that is NOT within the specified range?
When you copy and paste the "entire row" then the pasted
data will include Cell D100 and, presumably, other data
associated with Cell D100 contained in the same row. This
seems unreasonable. Did you really mean the entire row?

Q2:
Are the data sorted in ascending or descending order?

Q3:
Roughly how much data are we talking about?

Q4:
Are there gaps or potentially gaps in the data?

Q5:
Are there other numeric values on the worksheet (Note that
date values can be evaluated as numeric).

Q6:
What if a date value on the worksheet is in the wrong
format or is misspelled? For instance, "Jan. 2, 2004"
isn't recognized as a date by Excel and can't be
programmatically evaluated as a number.

Q6:
I was thinking that probably the best way to obtain the
After and Before date ranges is to use multiple dropdown
lists the same as what you see when you click the Search
button adjacent the New Post and Post Reply buttons of
this website. Does this method appeal to you? The
dropdown lists could be either worksheet based (I prefer
Data Validation In-cell) or user form list boxes. The
alternative of prompting the user to type in the dates in
an input box risks spelling error and is a little more
work.

Note that I work for a living and probably won't be able
to respond until tomorrow night. Hopefully someone else
will take over once things have been clarified.

Regards,
Greg
 
C

Celeste

Thanks Greg for responding so quickly.

Q1: You're right - it could imply a comflict. Let's say Cell A100
has a date that matches the range and D100 does not. Could it be
programmed that all dates that do not match the input date range be
'blanked out'? Then the entire row can be copied to new spreadsheet
and only the dates within the input range will be copied.

Q2: Ascending

Q3: Not much, possibly 1000-2000 rows

Q4: No gaps or potential gaps

Q5: Yes, there are other numberic values. They are formatted as
Number<General.

Q6: The dates will always be in mm/dd/yyyy format.

Comment: I have some code that will prompt users for the date range
and will check to ensure the begin date is not after the end date.
Could the rest of the code be wrapped around this? It will be
difficult to have the validation code spreadsheet based seeing I'll
need to run this macro on different downloads, although the number and
sequence of the columns will always be the same.


Dim StartDate As Date
Dim EndDate As Date
Dim SearchCell As Date
Dim Difference As Integer

'will resume if Error encountered ie. End Date falls before Start Date
StartAgain:

'prompt user for Start Date
Do
StartDate = InputBox(Prompt:="Enter Start Date (mm/dd/yyyy) ie.
03/27/2003:" & vbCrLf)
If Not IsDate(StartDate) Then MsgBox "Wrong Date Type - Enter
Start Date (mm/dd/yyyy) ie. 27/03/2003"
Loop Until IsDate(StartDate)

'prompt user for End Date
Do
EndDate = InputBox(Prompt:="Enter End Date (mm/dd/yyyy) ie.
03/27/2003:" & vbCrLf)
If Not IsDate(EndDate) Then MsgBox "Wrong Date Type - Enter End
Date (mm/dd/yyyy) ie. 03/27/2003"
Loop Until IsDate(EndDate)

'display Start & End dates entered
MsgBox "Start Date is: " & StartDate
MsgBox "End Date is: " & EndDate

'calculate difference between Start Date and End Date
Difference = DateDiff("d", StartDate, EndDate)

'if negative - End date before Start date
'display error message and start again
If Difference < 0 Then
MsgBox "Error- End Date before Start Date!- Start Again"
MsgBox "Date Difference:" & Difference
GoTo StartAgain
End If
 
Top