Prev: Checking latest Forefront updates
Next: Message still appears with DisplayAlerts = False & SaveChanges:=Fa
From: Bradly on 4 Mar 2010 15:50
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.

From: OssieMac on 5 Mar 2010 03:07
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.
>
From: Bradly on 8 Mar 2010 11:46
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.
> >
From: OssieMac on 9 Mar 2010 04:10
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.
> > >
From: OssieMac on 9 Mar 2010 06:51
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


 |  Next  |  Last
Pages: 1 2
Prev: Checking latest Forefront updates
Next: Message still appears with DisplayAlerts = False & SaveChanges:=Fa