Find function finds the incorrect date.

[cosmarchy]

I've tried using the code below, in various formats as you can see from the commented out code, to try and find the address of the cell in the first row which contains the current month of the year.

Private Sub Find_test()

    Dim dteTodaysMonth As Date

    dteTodaysMonth = Format(Now, "mmmm - yyyy")
    'dteTodaysMonth = DateSerial(Format(Now, "yyyy"), Format(Now, "mm"), 1)
    
    With Worksheets("Sheet1").Rows(1)
        'Debug.Print .Find(What:=dteTodaysMonth, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Address
        'Debug.Print .Find(What:=DateValue(dteTodaysMonth), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Address
        Debug.Print .Find(What:=DateSerial(Format(Now, "yyyy"), Format(Now, "mm"), 1), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Address
    End With

End Sub

All the months in the first row display as 'January - 2020', 'February - 2020' etc.

However, the code always displays the date of the cell with 'December - 2020' in it and not February as you'd expect. Now I'm sure it is an issue to do with the way that date/time is stored but I'm at a loss as to how to fix this issue.

Any help is appreciated. Thanks

 

[JoeMo]

The Find Method always uses a text comparison. If your formatting is like January - 2020 and so on, you want to look for a match in the .Text value of each cell, not in the serial date number. See if this works for you:

Sub Find_test()
    Dim M As String, Y As String, dteTodaysMonth, Fnd as Range
    M = MonthName(Month(Date))
    Y = Year(Date)
    
    dteTodaysMonth = CStr(M & " - " & Y)
'    dteTodaysMonth = Format(Now, "mmmm - yyyy")
    'dteTodaysMonth = DateSerial(Format(Now, "yyyy"), Format(Now, "mm"), 1)
    
    With Worksheets("Sheet1").Rows(1)
        'Debug.Print .Find(What:=dteTodaysMonth, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Address
        'Debug.Print .Find(What:=DateValue(dteTodaysMonth), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Address
'        Debug.Print .Find(What:=DateSerial(Format(Now, "yyyy"), Format(Now, "mm"), 1), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Address
        
        Set Fnd = .Find(dteTodaysMonth, LookIn:=xlValues)
        If Not Fnd Is Nothing Then
            MsgBox Fnd.Address
        Else
            MsgBox "Can't find today's date in row 1"
        End If
    End With

End Sub

 

[JLGWhiz]

If your entries in row 1 are actual date type dates, then maybe this:
Try chaning you LookIn:= parameter;

Debug.Print .Find(What:=dteTodaysMonth, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Address[/Code[

 

[cosmarchy]

Thanks for your replies guys but unfortunately neither @JoeMo nor @JLGWhiz code actually finds the correct date.

With Joemo's code, I get the "Can't find today's date in row 1" message and JLGWhiz's code I get the 'object variable or with block variable not set' error.

 

[Fluff]

You're code works for me as long as row 1 contains actual dates like 01/01/2020, 01/02/2020 etc. regardless of date format in the cell.

 

[cosmarchy]

@Fluff ****, that's a bit annoying
Here is what I get from the JoeMo's and JLGWhiz's respective code:

 

[Fluff]

Firstly, I would advise against using merged cells, as they will only cause you problems & grief.
With your code I get $BK$1 what do you get?
Also where are you UK, US, Australia, or ?

 

[cosmarchy]

@Fluff - I dont get anything other than the 'object variable or with block variable not set' error or the 'cant find todays data' message box...

I'm based in the UK.

 

[Fluff]

What exactly do you have in row 1? If you select the row & change the format to "General", what do you see?

 

[cosmarchy]

This is what I get: