Search records with dates, then Group by month

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mjkunkel@gmail.com - 13 Jul 2007 18:27 GMT

I'm writing a macro that searches a spreadsheet for dates. I want to
find records that are associated with a certain month. The dates in
the spreadsheet are in the mmddyyyy format. How can I write code that
would recognize any date within a given month and return true. For
example, 7/12/2007 or 7/2/2007 would both return "true" for the month
of July. 5/5/2007 or 5/27/2007 would return "true" for May, etc.

thanks

Reply to this Message

CLR - 13 Jul 2007 19:36 GMT

You could use the Autofilter to give you a LessthanGreaterthan range.
Otherwise, keying just on Months, you would get May06, May07, etc if your
database spanned more than one year. Use of the Autofilter will also allow
extracting lesser periods, like two weeks, or greater periods, like 3 months,
etc.

hth
Vaya con Dios,
Chuck, CABGx3

> I'm writing a macro that searches a spreadsheet for dates. I want to
> find records that are associated with a certain month. The dates in
[quoted text clipped - 4 lines]
>
> thanks

Reply to this Message

OssieMac - 15 Jul 2007 04:38 GMT

The Find Method recognises the wildcard * to replace any number of characters
and ? where you want to replace a specific number of characters. You can use
strings to search in the values.

Example:-

Dim strDate As String

'for dates formatted as "mmddyyyy"
strDate = "02??2007" 'Replace characters for day of month with ??

Cells.Find(What:=strDate, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

'for dates formatted as "mmm dd yyyy"
strDate = "feb*2007" 'Replace day of month and spaces with *

'for dates formatted as "mm dd yyyy"
strDate = "feb????2007" 'Replace both the spaces and the day of month

Note: if you use numerics and not alphas for month, use the ? method for the
specific number of characters or you will run into problems with confusion
between the number of the month and the day of the month.

Also note that you use xlValues and xlPart.

Did you also know that the find method always remembers the arguments from
the previous find and if they are not specifically set in the macro it will
use the values last used in the session of xl even if used in the find in the
interactive mode so always set all of the arguments. That is why Find Next
method in VBA does not need the arguments to be reset after the initial find.

Hope this helps.

Regards,

OssieMac

> You could use the Autofilter to give you a LessthanGreaterthan range.
> Otherwise, keying just on Months, you would get May06, May07, etc if your
[quoted text clipped - 14 lines]
> >
> > thanks

Reply to this Message

OssieMac - 15 Jul 2007 04:58 GMT

An afterthought in case you need help in extracting the month and year from a
specific date to create the string to find. The following example should help.

Dim initialDate As Date
Dim searchMonth As String
Dim searchYear As String
Dim strDate As String

initialDate = #2/19/2007# ' Assigns a date
searchMonth = Month(initialDate) ' MyMonth contains 2.
searchYear = Year(initialDate) 'searchYear contains 2007

searchMonth = Format(searchMonth, "00") 'Change format of 2 to 02

strDate = searchMonth & "??" & searchYear

Regards,

OssieMac

> The Find Method recognises the wildcard * to replace any number of characters
> and ? where you want to replace a specific number of characters. You can use
[quoted text clipped - 53 lines]
> > >
> > > thanks

Reply to this Message