Finding a date in a column via VBA

[Papa_Don]

Hello Group,

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!

Don Wilson

[Stuugie]

Hi Don,

I'm not sure if what I have will help but if you select the date to find and then run the code, it should find it on the other sheet. You'll probably need to changed the sheet names and such to fit your requirements. Let me know how it goes:

Sub FindDates()

    Dim wS1 As Worksheet, wS2 As Worksheet
    Dim oC As Range
    Dim fStr As String, hStr As String
    Dim rowA As Long

    Set wS1 = Worksheets("Sheet1"): Set wS2 = Worksheets("Sheet2")
    fStr = ActiveCell.Text

    For Each oC In wS2.Range(wS2.Cells(1, 1), wS2.Cells(wS2.Rows.Count, 1).End(xlUp))
        If Mid(fStr, 2, 1) <> "-" Then
            If Left(oC.Text, 6) = fStr Then
                rowA = oC.Row
                Debug.Print rowA
            End If
        Else
            'Debug.Print Left(oC.Text, 5)
            If Left(oC.Text, 5) = fStr Then
                rowA = oC.Row
                Debug.Print rowA
            End If
        End If
    Next oC

End Sub

[Papa_Don]

For what it's worth, I've discovered that the formatting of what I'm searching with has to be in the same format as what I'm searching for. In other words if the search criteria is '04-OCT' then the date on sheet2 needs to be in the same format (which I thought was odd). I was truly under the impression that a date, regardless of how it was formatted on the spreadsheet, was being read as an integer representing the number of days beyond 1/1/1900. However I found that to be untrue.

Stuugie, thanks for the help. Although I didn't use your code, I do like the way you wrote it.