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

 

[Fluff]

Ok, that's fine, as it's a real date. Unfortunately I don't know what to suggest as this works for me

Debug.Print .Find(What:=DateSerial(Format(Now, "yyyy"), Format(Now, "mm"), 1), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Address

 

[JLGWhiz]

I think getting rid of the merged cells that the dates are in would be a good start. VBA and merged cells do not have a happy relationship because VBA does not know which cell in the merger it us supposed to interact with. One way to handle that is to not use merged cells in the data base and if the boss wants his data formatted, the extract it from the database into a formatted report to give to the boss.

 

[steve the fish]

I usually convert dates to long such as:

Dim dteToday As Long, dteMatch

dteToday = DateSerial(Year(Now), Month(Now), 1)

With Worksheets("Sheet1")
    dteMatch = Application.Match(dteToday, .Rows(1), 0)
    If IsNumeric(dteMatch) Then Debug.Print .Cells(1, dteMatch).Address
End With

 

[cosmarchy]

@JLGWhiz Real shame to have to do so... for me it ought to return a cell even if it isnt the correct one due to the merge. It seems very wrong of Excel to not find the very date I am sitting there staring at.

More worrying though is that Fluff has this working and I cannot and we are using the same code!!

 

[JLGWhiz]

@JLGWhiz Real shame to have to do so... for me it ought to return a cell even if it isnt the correct one due to the merge. It seems very wrong of Excel to not find the very date I am sitting there staring at.

More worrying though is that Fluff has this working and I cannot and we are using the same code!!

I don't believe @Fluff is using merged cells in her test set up. But I could be wrong.

 

[steve the fish]

Im not sure merged cells matter.

 

[JLGWhiz]

Im not sure merged cells matter.

They might not, depending on the code or formula and whether it is doing anything more than looking for a value in that range of cells, But it can be a real pill if the code tries to return a value, or use those cells in a filter, etc. VBA will not execute many commands on merged cells. Even if it returns the value, it returns it for the left top cell of the merged group and could be completely misleading for correlating any data in columns underneath the merged range.

 

[Fluff]

Whilst I agree that merged cells are a bad idea & should be removed, I did test the OP's code using merged cells & it worked for me with UK dates.