Problem with "Find" method and Date values

[SBF12345]

I am receiving "find" method results that are inaccurate. I am using date values of the type MM/DD/YY and searching in another worksheet in the same workbook with date values like MM/DD/YY. Most of the date values are returning values as you would expect, except for values beginning with "02" which are returning values starting with "12". I've not found instances of this error online and am totally puzzled as to why it is occurring. Any ideas as to why this is occurring and how it can be affected to return matching input and output dates everytime?

 

[KolGuyXcel]

Would it be possible to share the formula/ code?

 

[SBF12345]

sure thing...a little more info...I have stepped through the code below and observed variable values at the specific dates on the worksheet that are outputting dates inconsistent with the input values. "02" month vs "12" month. it appears as though the variable value inputs into the "find" method are valued the same as on the worksheet. The output variables after the "find" method line runs returns a value different from the value associated with the date that is referenced on the worksheet. The end result evidence of an error in the values are charts with different numbers of observations (~200 days)

Dim aa As IntegerDim StartDate As String
Dim EndDate As String
Dim RngStart As Range
Dim RngEnd  As Range
Dim RngStartR As String
Dim RngStartRng As Range
Dim RngEndR As String
Dim RngEndRng As Range
Dim RngXR As String:            RngXR = ActiveWorkbook.Sheets("ActiveSheet").Range("C7").value
Dim RngXR2 As String:           RngXR2 = ActiveWorkbook.Sheets("ActiveSheet").Range("C8").value
Dim sh As Worksheet
Dim chrt As ChartObject
Dim ch As Chart
Dim zz As Integer
Dim NumObs2 As Long


'ActiveWorkbook.Sheets("AllDistanceMeasures").Range("I:I,J:J").NumberFormat = "0"


'ActiveWorkbook.Sheets("ActiveSheet").Range("E:E").NumberFormat = "0"


NumObs2 = Sheets("AllDistanceMeasures").Cells(Rows.Count, 10).End(xlUp).Row


For aa = 5 To NumObs2


    StartDate = Sheets("AllDistanceMeasures").Cells(aa, 9).value
    
    EndDate = Sheets("AllDistanceMeasures").Cells(aa, 10).value
   
    
        If StartDate <> "" Then
        
            Set RngStart = Sheets("ActiveSheet").Cells.Find(What:=StartDate, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Offset(0, 1)
            
        Else
            MsgBox "StartDate variable for " & Sheets("AllDistanceMeasures").Cells(aa, 9).Address & " not found", vbExclamation
            Exit Sub
            
        End If
        
        RngStartR = RngStart.Address
        
        If EndDate <> "" Then
        
            Set RngEnd = Sheets("ActiveSheet").Cells.Find(What:=EndDate, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Offset(0, 1)
            
        Else
            MsgBox "EndDate variable for " & Sheets("AllDistanceMeasures").Cells(aa, 10).Address & " not found", vbExclamation
            Exit Sub
            
        End If
        
        RngEndR = RngEnd.Address
       
    ActiveWorkbook.Sheets("LowDistCharts").Activate
       
    Set sh = Worksheets("LowDistCharts")
    Set chrt = sh.ChartObjects.Add(0, 0, 300, 300)
    Set ch = chrt.Chart
    
    Do While ch.SeriesCollection.Count > 1
    
        ch.SeriesCollection(1).Delete
    
    Loop
    
        With chrt
            .Height = 300
            .Width = 300
            .Top = 1 + ((aa - 4) * 300)
            .Left = 1
        End With
        
        With ch
            .HasTitle = True
            .ChartTitle.Text = aa & " " & StartDate & " to " & EndDate
            .ChartTitle.Font.Size = 8
            .ChartType = xlLine
            .SeriesCollection.NewSeries
            .SeriesCollection(1).Values = ActiveWorkbook.Worksheets("ActiveSheet").Range(RngXR, RngXR2)
            .SeriesCollection.NewSeries
            .SeriesCollection(2).Values = ActiveWorkbook.Worksheets("ActiveSheet").Range(RngStartR, RngEndR)
            .SeriesCollection(2).AxisGroup = 2
            .SeriesCollection(3).Delete
            .HasLegend = False
        End With
        
        For zz = 0 To NumObs - 1
        
            Sheets("ActiveSheet").Range(RngEndR).Offset(zz, 0).Copy
            Sheets("LowDistCharts").Cells(5, aa + 5).Offset(zz, 0).PasteSpecial xlPasteValues
            
        Next zz
    
Next aa

 

[SBF12345]

all the dates below are searched in either of the "find" methods in the code above. All dates beginning with "02" return dates beginning with "12" except the date "02/20/08". It appears to be the only set that includes a date beginning with "02" that is returning the correct range of values. Date inputs are below:

07/22/10	08/25/10
10/27/10	12/01/10
11/18/09	12/23/09
01/06/10	02/10/10
05/09/08	06/13/08
02/20/08	03/26/08
07/26/18	08/29/18
05/06/15	06/10/15
05/03/10	06/07/10
02/18/15	03/24/15
04/15/11	05/20/11
02/13/17	03/20/17
10/25/16	11/29/16
01/08/14	02/12/14
06/21/12	07/26/12
05/26/11	06/30/11
09/10/08	10/14/08
03/31/09	05/05/09
04/04/17	05/09/17
06/01/18	07/06/18

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[SBF12345]

ridiculous amounts of time consumed with this error. change xlpart to --> XLWHOLE.