I already demo'd to you that the format command works. The immediate window
result is a successfully executed VBA statement. I am not sure how you are
testing it, but apparently not correctly or in the proper context. (the equal
sign is superflous and was just to show it used at the end of the criteria1
statement although it would probably be concatenated with a Less than, less
than or equal to, Greater than, or Greater than or Equal to sign based on
your description. Working with dates and an autofilter can be troubling
with various regional settings, so it may take some experimentation on your
part, but it doesn't appear you have gotten that far.
Perhaps you are relegated to using the
DateSerial(year(date),month(date)-18,day(date)) portion of my answer and
looping row by row through your data making the decision to delete or not.
If
rngCell.NumberFormat = "mmm - yy"
correctly formats the cells, then the value is stored as a true date serial
number. The alternative is it is stored as a text string, but number
formats don't work on text strings.
So it appears the ball rests in your court to clean up your data and
determine how you will use the information imparted.
--
regards,
Tom Ogilvy
"bodhisatvaofboogie" wrote:
> Two issues, first:
>
> = format(DateSerial(year(date),month(date)-18,day(date)),"m/d/yyyy")
>
> The code bugs out on "m/d/yyyy" It highlights the m and says that it should
> be at the end of the statement. When the quotes are removed, it doesn't bug
> out but it appears to search for it, unfortunately it doesn't find anything.
> So what am I missing here?
>
>
> Second potential Issue,
>
> After checking the format of the column in question I noticed that it is not
> a "true" date, or appears not to be. The original date information from
> whence the data came had to be formatted into a readable form. The original
> data would look as this:
>
> 0697 when imported into excel. For easy reading, I wanted to turn that
> data into a readable date. The issue was that upon import from the other
> souce, the data was not readable as a date by excel, thus it couldn't be
> simply formatted. SO:
>
> I had to use this formula
>
> Dim rngCell As Range
> Dim rngStart As Range
> Dim intMaxRow As Integer
> Dim intCtr As Integer
>
> With ActiveSheet
> intMaxRow = .UsedRange.Rows.Count
> Set rngStart = ActiveCell
> For intCtr = 1 To (intMaxRow - 1)
> Set rngCell = rngStart.Offset(RowOffset:=intCtr)
> If IsNumeric(rngCell) And rngCell.Value > 0 Then
> On Error Resume Next
> rngCell.Value = DateValue(Int(rngCell / 100) & "/1/" & rngCell Mod
> 100)
> rngCell.NumberFormat = "mmm - yy"
> On Error GoTo 0
> End If
> Next intCtr
> End With
> Set rngStart = Nothing
> Set rngCell = Nothing
>
> to turn it into
>
> 6/1/1997
>
> Does that make any sense?
>
> SO, I guess I'm at square one, is there something I'm doing wrong or being
> too difficult?
>
> Thanks again!!!
>
>
> "Tom Ogilvy" wrote:
>
> > Nice ambiguous date examples. It is easy to see whether your dates are m/d
> > or d/m
> >
> > In the same spirit, turn on the macro recorder and apply an autofilter to
> > your data. Select the appropriate criteria. Delete the visible rows.
> >
> > Now turn off the macro recorder and look at the recorded code.
> >
> > Replace the Criteria with something akin to
> >
> > = format(DateSerial(year(date),month(date)-18,day(date)),"m/d/yyyy")
> >
> > From the immediate window for demo:
> > ? format(DateSerial(year(date),month(date)-18,day(date)),"m/d/yyyy")
> > 12/27/2005
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "bodhisatvaofboogie" wrote:
> >
> > > I have a macro that ends with a resulting list of parts. One column has a
> > > Date Added column and I want to select specific dates within it. I want
> > > parts with a date of the last 18 months selected and deleted. Now I'm not
> > > certain what the code would look like in the macro. Is there a way to select
> > > everything with the date of the last 18 months?
> > >
> > > The date is in this format:
> > >
> > > 1/1/2006 = January 1, 2006
> > > 12/12/2006 = December 12, 2006
> > >
> > > etc.
> > >
> > > Now I could put it in there probably to select the last 18 months from this
> > > date, though that would hardly be automated, because next month I would have
> > > to go into the code and modify it, then the next month, and so on. I wanted
> > > a universal way of doing it so the last 18 months regardless of the month I'm
> > > in will be selected and deleted. make sense?
> > >
> > > Thanks for the help!!!!
> > >
> > >
|
Similar Threads
