I'm writing a macro to match dates from one spreadsheet to another. Specificially, SpreadsheetA has the date listed as text. I want to match that date to a range of dates in a column to find the row the matching date is on.
I have some things that I need to be prepared to overcome:
SpreadsheetA has the date I'm looking for and it's formatted as "20-SEP-2014". SpreadsheetB has all of the dates in my range formatted as "20-Sep". I've taken account for this in my code:
strngDate = Right(findDate, 11) strngDate = Left(strngDate, 6) rowA = Columns("A:A").Find(What:=strngDate, After:=[A2], LookIn:=xlValues, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, **MatchCase:= False**, _ SearchFormat:=False).Row
My challenge now is to eliminate the worry of the date formatting on spreadsheetB. In otherwords is there a way that I can convert everything to the date-serial-number ensure that, regardless how the date is formatted, the routine will always find the correct row?
In advance, thanks for your answers!