Find Date row error on Find

  • I have an odd problem that I have noticed on two worksheets. I have a sheet with dates for the year entered in column A3 and down, starting at 1/1 and going to 12/31. I then have a userform where the user selects a date from a DTPicker and the macro locates the row for the selected date to perform various functions in that row. The problem I have is that when 1/1/YY is selected, the find method always selects the row for date 11/1/YY. I have no idea why it does that and have to manually correct for it ATM. Any insight would be appreciated.


    [VBA]Sub Update_Charts_bydates(DtStart As Date, DtEnd As Date)


    Dim Ws_1 As Worksheet, Ws_2 As Worksheet
    Dim Ch_1 As Chart, Ch_2 As Chart
    Dim rngTATdata As Range, rngTATlabels As Range, rngRFTdata As Range, rngRFTlabels As Range
    Dim iDtEnd As Integer, iDtStart As Integer


    Application.ScreenUpdating = False


    Set Ws_1 = Worksheets("Historical") 'sheet with dates entered down starting at A3
    Set Ch_1 = Charts("TAT")
    Set Ch_2 = Charts("%InSpec")


    Set DateHeader = Ws_1.Range(Ws_1.Range("A3"), Ws_1.Range("A3").End(xlDown)) 'set range to find date


    On Error GoTo ErrHandler
    If Month(DtStart) = 1 And Day(DtStart) = 1 Then '1/1/YY likes to find 11/1/YY instead, so manually correct
    iDtStart = 3
    Else
    iDtStart = DateHeader.Find(DtStart).Row
    End If
    iDtEnd = DateHeader.Find(DtEnd).Row


    '---------------- More code below but not shown
    On Error GoTo 0[/VBA]


    If you really want to see the sheet I can attach a desensitized version upon request

  • Have you tried using the full set of parameters of the Range.Find Method ?
    Note that some parameters are persistent and don't revert to a default value so unless you specify them you may not be using .Find the way you think.


    What happens if you change the start of the DateHeader range to A1 or A2 ?

  • I tried playing with the full settings but kept getting an error code of 91 "Object variable or With block variable not set". Not really sure why since nothing really changed. On another workbook I have basically the same setup but starting at A2 with the same issue. It's almost like it doesn't actually see the 1/1/YY

  • Quote

    On another workbook I have basically the same setup but starting at A2 with the same issue. It's almost like it doesn't actually see the 1/1/YY


    It doesn't sound like you tried changing the start of Set DateHeader from A3 to A2.
    Did you read the description of the After parameter at the link provided ?

  • Sorry I misunderstood what you meant. By changing the start of the range from the first cell to the header it now works. I find that odd that it would not search the first cell of the range since it is part of the search area. I tried messing with the after parameter but kept getting that error code of 91.


    thanks for the help NoSparks

  • In the description of the After parameter at the link provided. It says


    [SIZE=14px]The cell after which you want the search to begin...
    Notice that After must be a single cell in the range.
    Remember that the search begins after this cell;
    the specified cell isn't searched until the method wraps back around to this cell.
    If you do no specify this argument, the search starts after the cell in the upper-left corner of the range.[/SIZE]


    Something in your parameter settings, likely LookAt, which is a persistent parameter, was last used to find xlPart and is now determining 11/1/YY is a match so the .Find never wraps back around to the first cell.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!