Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with dates in the past
Hi.
I currently have a code that correctly filters the case reviews due for a given month (for my example, it is for March). I want, however, to make a file that filters all of the past due case reviews. Here is my current code: Sub GetOverdueReviews() ' ' GetOverdueReviews Macro ' ' ' Sheets("F Cases").Activate Application.Goto Reference:="R1C1" Dim FilterRange As Range Dim CopyRange As Range Set FilterRange = Range("L1:L5000") 'Header in row Set CopyRange = Range("A1:M5000") FilterRange.AutoFilter Field:=1, Criteria1:="03 10" CopyRange.SpecialCells(xlCellTypeVisible).Copy _ Destination:=Worksheets("Overdue Reviews").Range("A3") Application.CutCopyMode = False Sheets("F Cases").Activate Selection.AutoFilter Application.Goto Reference:="R1C1" Sheets("Overdue Reviews").Activate Application.Goto Reference:="R1C1" End Sub How can this be modified to get all of the past due case reviews (they could go back as far as 10/2008)? The date format used in my code is "03 10". Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with dates in the past
Hi Bradley,
Am I correct in assuming that you want to include all data prior to and including a specific month? If the dates in the filtered column are actual dates and formatted to mm yy then you may only have to change the filter criteria to the following. FilterRange.AutoFilter Field:=1, Criteria1:="<=Mar-2010" Excel VBA has problems with numerics in dates when the date to be entered is a string like used with AutoFilter code. However, I have always been successful by using the alpha literal month. (For interest in the above, Excel actually evaluates Mar-2010 as 1- Mar-2010.) If the above does not work and your regional date format is m/d/y and the filtered column contains actual dates and formatted to mm yy then try the following. FilterRange.AutoFilter Field:=1, Criteria1:="<=Mar-31-2010" If the above does not work then get back to me with some answers to the following questions. What version of xl are you using? Are the values in the filtered column actual dates and formatted as mm yy or are they simply text? (I can tell you how to change from text to date format and display as mm yy but I need an answer to the following question first.) What is your regional date format (d/m/y or m/d/y)? -- Regards, OssieMac "Bradly" wrote: Hi. I currently have a code that correctly filters the case reviews due for a given month (for my example, it is for March). I want, however, to make a file that filters all of the past due case reviews. Here is my current code: Sub GetOverdueReviews() ' ' GetOverdueReviews Macro ' ' ' Sheets("F Cases").Activate Application.Goto Reference:="R1C1" Dim FilterRange As Range Dim CopyRange As Range Set FilterRange = Range("L1:L5000") 'Header in row Set CopyRange = Range("A1:M5000") FilterRange.AutoFilter Field:=1, Criteria1:="03 10" CopyRange.SpecialCells(xlCellTypeVisible).Copy _ Destination:=Worksheets("Overdue Reviews").Range("A3") Application.CutCopyMode = False Sheets("F Cases").Activate Selection.AutoFilter Application.Goto Reference:="R1C1" Sheets("Overdue Reviews").Activate Application.Goto Reference:="R1C1" End Sub How can this be modified to get all of the past due case reviews (they could go back as far as 10/2008)? The date format used in my code is "03 10". Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with dates in the past
Hi. I tried the two FilterRange options you gave--the first one didn't do
anything and the second one listed the majority of the original list, which I know is not correct. I tried changing the date format of the column, and it showed it originally listed as "general"--I am thinking the dates that I import (mm yy) are probably text. The dates didn't change when I changed the column date format. I am using Excel 2003 and the regional date settings are m/d/y. Let me know if there is anything else you need to help. Thanks. "OssieMac" wrote: Hi Bradley, Am I correct in assuming that you want to include all data prior to and including a specific month? If the dates in the filtered column are actual dates and formatted to mm yy then you may only have to change the filter criteria to the following. FilterRange.AutoFilter Field:=1, Criteria1:="<=Mar-2010" Excel VBA has problems with numerics in dates when the date to be entered is a string like used with AutoFilter code. However, I have always been successful by using the alpha literal month. (For interest in the above, Excel actually evaluates Mar-2010 as 1- Mar-2010.) If the above does not work and your regional date format is m/d/y and the filtered column contains actual dates and formatted to mm yy then try the following. FilterRange.AutoFilter Field:=1, Criteria1:="<=Mar-31-2010" If the above does not work then get back to me with some answers to the following questions. What version of xl are you using? Are the values in the filtered column actual dates and formatted as mm yy or are they simply text? (I can tell you how to change from text to date format and display as mm yy but I need an answer to the following question first.) What is your regional date format (d/m/y or m/d/y)? -- Regards, OssieMac "Bradly" wrote: Hi. I currently have a code that correctly filters the case reviews due for a given month (for my example, it is for March). I want, however, to make a file that filters all of the past due case reviews. Here is my current code: Sub GetOverdueReviews() ' ' GetOverdueReviews Macro ' ' ' Sheets("F Cases").Activate Application.Goto Reference:="R1C1" Dim FilterRange As Range Dim CopyRange As Range Set FilterRange = Range("L1:L5000") 'Header in row Set CopyRange = Range("A1:M5000") FilterRange.AutoFilter Field:=1, Criteria1:="03 10" CopyRange.SpecialCells(xlCellTypeVisible).Copy _ Destination:=Worksheets("Overdue Reviews").Range("A3") Application.CutCopyMode = False Sheets("F Cases").Activate Selection.AutoFilter Application.Goto Reference:="R1C1" Sheets("Overdue Reviews").Activate Application.Goto Reference:="R1C1" End Sub How can this be modified to get all of the past due case reviews (they could go back as far as 10/2008)? The date format used in my code is "03 10". Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with dates in the past
Hi again Bradley,
Insert a column beside the date column and format it to date. I assume you have column headers and the first row of data is in row 2. In the following formula I have used N2 for the text data. Replace N with whatever column your current text dates are in. Insert the following formula in the new column and copy it down. =DATEVALUE(LEFT(N2,2)&"/"&"01/"&RIGHT(N2,2)) The formula should return a date as the 1st day of month. Select the column and format it to mm/yy (or mmm yy if you want the alpha abbreviation of the month.) You can record you code to set the Filter criteria but edit the criteria in the code as per my previous post. You might have to use custom criteria when setting and set it to less than apr 1 2010 because mar 2010 translates to mar 1 2010 in the code.) -- Regards, OssieMac "Bradly" wrote: Hi. I tried the two FilterRange options you gave--the first one didn't do anything and the second one listed the majority of the original list, which I know is not correct. I tried changing the date format of the column, and it showed it originally listed as "general"--I am thinking the dates that I import (mm yy) are probably text. The dates didn't change when I changed the column date format. I am using Excel 2003 and the regional date settings are m/d/y. Let me know if there is anything else you need to help. Thanks. "OssieMac" wrote: Hi Bradley, Am I correct in assuming that you want to include all data prior to and including a specific month? If the dates in the filtered column are actual dates and formatted to mm yy then you may only have to change the filter criteria to the following. FilterRange.AutoFilter Field:=1, Criteria1:="<=Mar-2010" Excel VBA has problems with numerics in dates when the date to be entered is a string like used with AutoFilter code. However, I have always been successful by using the alpha literal month. (For interest in the above, Excel actually evaluates Mar-2010 as 1- Mar-2010.) If the above does not work and your regional date format is m/d/y and the filtered column contains actual dates and formatted to mm yy then try the following. FilterRange.AutoFilter Field:=1, Criteria1:="<=Mar-31-2010" If the above does not work then get back to me with some answers to the following questions. What version of xl are you using? Are the values in the filtered column actual dates and formatted as mm yy or are they simply text? (I can tell you how to change from text to date format and display as mm yy but I need an answer to the following question first.) What is your regional date format (d/m/y or m/d/y)? -- Regards, OssieMac "Bradly" wrote: Hi. I currently have a code that correctly filters the case reviews due for a given month (for my example, it is for March). I want, however, to make a file that filters all of the past due case reviews. Here is my current code: Sub GetOverdueReviews() ' ' GetOverdueReviews Macro ' ' ' Sheets("F Cases").Activate Application.Goto Reference:="R1C1" Dim FilterRange As Range Dim CopyRange As Range Set FilterRange = Range("L1:L5000") 'Header in row Set CopyRange = Range("A1:M5000") FilterRange.AutoFilter Field:=1, Criteria1:="03 10" CopyRange.SpecialCells(xlCellTypeVisible).Copy _ Destination:=Worksheets("Overdue Reviews").Range("A3") Application.CutCopyMode = False Sheets("F Cases").Activate Selection.AutoFilter Application.Goto Reference:="R1C1" Sheets("Overdue Reviews").Activate Application.Goto Reference:="R1C1" End Sub How can this be modified to get all of the past due case reviews (they could go back as far as 10/2008)? The date format used in my code is "03 10". Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with dates in the past
I forgot to tell you that after copying the formula down the column to select
the new column and Copy - Paste Special - Values over top of itself to remove the formulas and then you can delete the old column of dates so that your worksheet appears the same as before. -- Regards, OssieMac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with dates in the past
This works great. Thanks.
"OssieMac" wrote: I forgot to tell you that after copying the formula down the column to select the new column and Copy - Paste Special - Values over top of itself to remove the formulas and then you can delete the old column of dates so that your worksheet appears the same as before. -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting for past due dates | Excel Discussion (Misc queries) | |||
record values from past dates | Excel Discussion (Misc queries) | |||
recognizing dates past End of Month | Excel Discussion (Misc queries) | |||
Past Due - Due Dates | Excel Worksheet Functions | |||
How to set up past due notices based on dates | Excel Discussion (Misc queries) |