Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding a date in a range (Excel 2003 SP2)

    All

    I have come across a bizarre problem trying to find a date within a range in code. This is the function I have created :

    Public Function FindDate(strdate As String) As Long
    Dim rng As Range
    Dim rngFound As Range
    Dim dtm As Date
    '
    With ActiveSheet
    Set rng = .Range(.Cells(5, 1), .Cells(750, 1))
    End With
    dtm = CDate(strdate)
    Set rngFound = rng.Find(what:=CDate(strdate), LookIn:=xlFormulas)
    If rngFound Is Nothing Then
    Debug.Print "Not found"
    FindDate = 999
    Else
    Debug.Print "Row = " & rngFound.Row
    FindDate = rngFound.Row
    End If
    End Function

    If I run this in the Immediate window it works perfectly, but if I call the function in a cell on the worksheet it finds nothing.

    However, if I change the .Find line to :

    Set rngFound = rng.Find(what:=format(strdate,"dd/mm/yyyy")), LookIn:=xlFormulas)

    it works perfectly on the worksheet, but the Immediate window shows "Not found".

    Can anyone explain the logic behind this, and suggest a version which would work both ways (worksheet and immediate) ?

    Usual thanks for your expert help

    Nick

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts

    Re: Finding a date in a range (Excel 2003 SP2)

    As a general rule, Excel VBA seems to assume 'American' dates throughout (i.e. mm/dd/yyyy) unless told otherwise.
    Thus it will happily work with "07/12/2006" although it assumes it is July 12th rather than the British 7th December.
    Interesting things happen with British dates after the 12th of the month.
    Working with dates in VBA can be a real pain and you must be very careful.

    zeddy

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding a date in a range (Excel 2003 SP2)

    This is because VBA is US-centric; the immediate window expects dates to be in US format. Unless you're willing to set mm/dd/yyyy as default date format on your system, you'll have this kind of incompatibility between use in a worksheet and use in the Immediate window.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a date in a range (Excel 2003 SP2)

    Hans & Zeddy

    Thanks for your responses. It seems as if there is no solution as such, just "Be Very Aware".

    If it wasn't a Microsoft product one would almost be tempted to call it a bug :-)

    Nick

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a date in a range (Excel 2003 SP2)

    This is the solution we came up with in the end. It's not very elegant but it does have the virtue of working :

    Public Function FindDate(strdate As String, blnCode As Boolean) As Long
    Dim rng As Range
    Dim rngFound As Range
    Dim dtm As Date
    '
    With ActiveSheet
    Set rng = .Range(.Cells(5, 1), .Cells(750, 1))
    End With
    If blnCode = True Then
    Set rngFound = rng.Find(what:=Format(strdate, "dd/mm/yyyy"), LookIn:=xlFormulas)
    Else
    dtm = CDate(strdate)
    Set rngFound = rng.Find(what:=dtm, LookIn:=xlFormulas)
    End If
    If rngFound Is Nothing Then
    Debug.Print "Not found"
    FindDate = 999
    Else
    Debug.Print "Row = " & rngFound.Row
    FindDate = rngFound.Row
    End If
    End Function

    blnCode is True if called from the worksheet, False if from the Immediate window

    Nick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •