[Anthonidas]

Hi Everybody

I'm getting crazy with that stuff and hope you can help me.

I have different cells in excel containing dates and when the opening-event is fired, i would like to find the cell containing today's date. To do so i use this code:

Set dCell = oSht.Range("A1:" & lastCell.Address).Find(What:=tDate, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

My problem is the date format. If i change the value in the find-function from "tDate" to "dd/mm/yyyy" it finds the cell, otherwise it doesn't. Where i live (Switzerland) the default date format is "dd.mm.yyyy" but even if use datevalue() or format(tDate, "dd/mm/yyyy") it still uses periods instead of slashes, because the variable tDate is defined as date. And as already said, if there is anything else than slashes it doesn't find the date.

How can i solve this problem?

This post has been edited by Anthonidas: 01 January 2013 - 04:54 PM

[maj3091]

I'm no expert in VBA, so take a look at this link and see if it helps at all regarding cell formatting for different regions.

Also, I thought the Date Value should be the same regardless of locale?

[andrewsw]

Try this:

    Dim rng As Range
    
    For Each rng In oSht.Range("A1:" & lastCell.Address)
        If IsDate(rng.Value) Then
            If CDate(rng.Value) = Date Then
                Set dCell = rng
                Exit For
            End If
        End If
    Next rng
    If dCell Is Nothing Then
        MsgBox "Not found"
    End If

[Anthonidas]

First of all: thank you very much for your support.

Then: After a day D.I.C. not working I found the solution to my problem. In fact my date cells were formatted with the non standard date format. After this change my script began to work.

Nevertheless, I do not understand the difference between the two date formats. If you look at the attached image, can you explain me why it works with the first format (the one with asterisk) but does not work with the visually identical format (red border)?

[andrewsw]

If someone uses the Control Panel to change the default date format then this will affect how dates (applied with the asterisk-ed * formats) are displayed in the worksheet.

My sample code explicitly converts to Date values, which should ignore any issues over the formatting of the dates.

This post has been edited by andrewsw: 02 January 2013 - 02:27 PM

[Anthonidas]

Ok, but why should this affect how dates are handled too? I mean, if a date is displayed as "dd.mm.yyyy" or "dd/mm/yyyy" or something else, this should not affect how the date is handled in the background, because finally it is only a way of displaying the same value... Or am I wrong?

[andrewsw]

Anthonidas, on 02 January 2013 - 02:34 PM, said:

Ok, but why should this affect how dates are handled too? I mean, if a date is displayed as "dd.mm.yyyy" or "dd/mm/yyyy" or something else, this should not affect how the date is handled in the background, because finally it is only a way of displaying the same value... Or am I wrong?

It shouldn't matter.. but Excel can be a bit funny about dates .

[Anthonidas]

andrewsw, on 02 January 2013 - 10:39 PM, said:

It shouldn't matter.. but Excel can be a bit funny about dates />.

Ah... this is the point.

Well, thank you again.