Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying on Data Ranges
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying on Data Ranges
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 -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying on Data Ranges
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 "Greg Wilson" wrote in message ... 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 -----Original Message----- 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error in querying data from MS Access | Excel Discussion (Misc queries) | |||
Querying data | Excel Discussion (Misc queries) | |||
Querying Internal Data | Excel Discussion (Misc queries) | |||
Querying data from Access | Excel Worksheet Functions | |||
Querying Data from Access | Excel Discussion (Misc queries) |