10 Replies - 20126 Views - Last Post: 14 April 2014 - 07:51 AM Rate Topic: -----

#1 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 31
  • View blog
  • Posts: 223
  • Joined: 25-April 11

Excel VBA Date Format - Find Dates In Cells

Posted 01 January 2013 - 04:54 PM

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


Is This A Good Question/Topic? 0
  • +

Replies To: Excel VBA Date Format - Find Dates In Cells

#2 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 303
  • View blog
  • Posts: 1,796
  • Joined: 26-March 09

Re: Excel VBA Date Format - Find Dates In Cells

Posted 02 January 2013 - 01:43 AM

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?
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3741
  • View blog
  • Posts: 13,086
  • Joined: 12-December 12

Re: Excel VBA Date Format - Find Dates In Cells

Posted 02 January 2013 - 12:52 PM

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

Was This Post Helpful? 1
  • +
  • -

#4 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 31
  • View blog
  • Posts: 223
  • Joined: 25-April 11

Re: Excel VBA Date Format - Find Dates In Cells

Posted 02 January 2013 - 02:17 PM

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)?
Posted Image
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3741
  • View blog
  • Posts: 13,086
  • Joined: 12-December 12

Re: Excel VBA Date Format - Find Dates In Cells

Posted 02 January 2013 - 02:26 PM

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

Was This Post Helpful? 0
  • +
  • -

#6 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 31
  • View blog
  • Posts: 223
  • Joined: 25-April 11

Re: Excel VBA Date Format - Find Dates In Cells

Posted 02 January 2013 - 02:34 PM

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?
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3741
  • View blog
  • Posts: 13,086
  • Joined: 12-December 12

Re: Excel VBA Date Format - Find Dates In Cells

Posted 02 January 2013 - 02:39 PM

View PostAnthonidas, 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 :dontgetit:.
Was This Post Helpful? 1
  • +
  • -

#8 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 31
  • View blog
  • Posts: 223
  • Joined: 25-April 11

Re: Excel VBA Date Format - Find Dates In Cells

Posted 02 January 2013 - 02:44 PM

View Postandrewsw, on 02 January 2013 - 10:39 PM, said:

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


Ah... this is the point. :surrender:

Well, thank you again.
Was This Post Helpful? 0
  • +
  • -

#9 kitty1010  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 14-April 14

Re: Excel VBA Date Format - Find Dates In Cells

Posted 14 April 2014 - 05:22 AM

hey, If you have problems with some functions in excel, help yourself with the following website: http://www.excel-aid...autofilter.html It shows you step by step how you have to work all of the functions, which maybe have not known.
Was This Post Helpful? 0
  • +
  • -

#10 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 31
  • View blog
  • Posts: 223
  • Joined: 25-April 11

Re: Excel VBA Date Format - Find Dates In Cells

Posted 14 April 2014 - 05:49 AM

Did you read the whole post?

firstly, this post is old (1 yr) and already resolved.
secondly, your answer is not of any help here, because it does not answer the the question that was originally asked.
Was This Post Helpful? 0
  • +
  • -

#11 kitty1010  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 14-April 14

Re: Excel VBA Date Format - Find Dates In Cells

Posted 14 April 2014 - 07:51 AM

View PostAnthonidas, on 14 April 2014 - 05:49 AM, said:

Did you read the whole post?

firstly, this post is old (1 yr) and already resolved.
secondly, your answer is not of any help here, because it does not answer the the question that was originally asked.


I'm sorry I just wanted to help:(
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1