locked
Cells.Find xlDate does not find first date in sheet unless date format is dd/mm/yyyy RRS feed

  • Question

  • Hello,

    I have an excel spread sheet that contains the following data:

    B1 = "01/10/2013"

    C1 = "02/10/2013"

    D1 = "03/10/2013"

    Cells are formatted with a custom format: dd-mmm-yy

    Public Sub Main()
        Dim loc As Range
        
        With Sheet1.Cells
            Set loc = .Find(What:=xlDate, LookIn:=xlValues, LookAt:=xlPart)
        End With
        
        MsgBox loc.Address
    End Sub
    

    loc.Address is returned as $C$1 but if I change the cell formatting to short date then loc.Address correctly returns $B$1.

    Is there anyway to find the first cell with a date in without changing the cell formatting? I am using .Find as the spreadsheet date location may change and eventually I want to open a worksheet (from another workbook), iterate through the date cells pulling the data in and the close it again. I don't control formatting of the worksheet to be read in and therefore would prefer not to have to open the worksheet and change the date format first.

    Cheers,

    Tom

    Wednesday, November 6, 2013 8:58 PM

Answers

  • Thanks - issue has been on another board. Unfortunately, I can't post the link as apparently I'm not verified yet.

    Hans was on the right lines - xlDate was being resolved to 2 and therefore .Find was picking up the 2 in 01/10/2013 or 02-Oct-13.

    Using Application.FindFormat.NumberFormat = "d-mmm-yy" does the trick nicely.

    Cheers,
    Tom

    • Marked as answer by AtTheControls Thursday, November 7, 2013 4:40 PM
    Thursday, November 7, 2013 4:40 PM

All replies

  • xlDate is a symbolic constant with value 2. Why are you searching for this constant?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 6, 2013 9:13 PM
  • Hi,

    Change instead of LookIn:=xlValues to LookIn:=xlFormulas

    Guy Zommer


    Guy Zommer

    Thursday, November 7, 2013 10:24 AM
  • Thanks - issue has been on another board. Unfortunately, I can't post the link as apparently I'm not verified yet.

    Hans was on the right lines - xlDate was being resolved to 2 and therefore .Find was picking up the 2 in 01/10/2013 or 02-Oct-13.

    Using Application.FindFormat.NumberFormat = "d-mmm-yy" does the trick nicely.

    Cheers,
    Tom

    • Marked as answer by AtTheControls Thursday, November 7, 2013 4:40 PM
    Thursday, November 7, 2013 4:40 PM