Search records with dates, then Group by month

  • Thread starter Thread starter mjkunkel
  • Start date Start date
M

mjkunkel

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
 
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
 
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
 
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
 
Back
Top