Date issues when using .Find in VBA (UK to US conversion)

[JackDanIce]

Hi,

I have a spreadsheet, where cells that will contain date values are already formatted to be UK style.

Using VBA, I search each row of date values to find a matching date and return the column number.

The search variable and intermediate window correctly shows the date. E.g. I'm searching for 26/04/2017, when I used debug.print arr(x,1) it shows as 26/04/2017

The range being searched contains this date and cell is showing it as 26/04/2017, again checking formatting, it's set to UK style.

I run the following function to return an array with column index numbers to matched date values.

Private Function ColNum(ByRef arr() As Variant, ByRef xRow As Long) As Variant

    Dim rngDate     As Range
    Dim LC          As Long
    Dim x           As Long
    Dim tempDate    As Date
        
    With Sheets("OR Sheet")
        LC = LastCol(Sheets("OR Sheet"), xRow) - 6
        If LC > 5 Then
            Set rngDate = .Cells(xRow, 7).Resize(, LC)
            Debug.Print rngDate.Address
            For x = LBound(arr, 2) To UBound(arr, 2)
                If IsDate(arr(1, x)) Then
                    On Error Resume Next
                    arr(1, x) = CLng(rngDate.Find(arr(1, x), , xlValues, xlWhole, xlByColumns).Column)
                    On Error GoTo 0
                    If Not IsNumeric(arr(1, x)) Then arr(1, x) = Empty
                End If
            Next x
            Set rngDate = Nothing
        End If
    End With
    
    ColNum = arr
    
End Function

When I print the array contents, all elements are empty with no column index values, EXCEPT when day = month (they have same values as each other).

Next, I press CTRL+F on the spreadsheet and the Find dialogue box is showing the last searched item as 4/26/2017 so somewhere/how using .Find through VBA, Excel is searching for 4/26/2017 which of course does not exist in the range; it appears to have swapped the month with the day and then treated it as a US date.

Anyone experienced this before and suggestions to resolve?

Thanks in advance,
Jack

 

[Marcelo Branco]

Nope did declare as variant!

Yes, i see also in Locals Window #26/04/2017#
But my code works fine.

M.

 

[Norie]

Can you post the code you are using to populate the array?

 

[JackDanIce]

Morning, based on Marcelo's function, this has same issue, in the CTRL+F Find dialogue box it's showing last searched value as 4/26/2017 but the array definitely contains 26/04/2017 with cell formatting and system settings set to UK style (DD/MM/YYYY)

 

[JackDanIce]

Included Application.FindFormat.Clear as well but issue persists, something, somewhere is switching from UK to US style after the code compiles and executes Updated code:

Private Function ColNum(ByRef arr() As Variant, ByRef xRow As Long) As Variant


    Dim rngDate As Range
    Dim rng     As Range
    Dim LC      As Long
    Dim x       As Long
        
    With Sheets("OR Sheet")
        LC = LastCol(Sheets("OR Sheet"), xRow) - 6
        Set rngDate = .Cells(xRow, 7).Resize(, LC)
        For x = LBound(arr, 2) To UBound(arr, 2)
            'Set rng = rngDate.Find(CDate(arr(1, x)), , xlValues, xlWhole, xlByColumns)
            Application.FindFormat.Clear
            Set rng = rngDate.Find(What:=CDate(arr(1, x)), After:=.Cells(7, 7), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
            On Error Resume Next
            arr(1, x) = IIf(Not rng Is Nothing, rng.Column, Empty)
            'arr(1, x) = IIf(IsDate(arr(1, x)), CLng(rngDate.Find(arr(1, x), , xlValues, xlWhole, xlByColumns).Column), Empty)
            On Error GoTo 0
            Set rng = Nothing
        Next x
    End With
    
    ColNum = arr
    
End Function

 

[JackDanIce]

This link: http://www.ozgrid.com/VBA/find-dates.htm suggests using "Short Date" as a format within Find; tried it in following code which does work for the first row of dates, but not for any rows after (blank cells are returned because .Find can't appear to find the date):

Private Function ColNum(ByRef arr() As Variant, ByRef xRow As Long) As Variant


    Dim rngDate As Range
    Dim rng     As Range
    Dim LC      As Long
    Dim x       As Long
        
    With Sheets("OR Sheet")
        LC = LastCol(Sheets("OR Sheet"), xRow) - 6
        Set rngDate = .Cells(xRow, 7).Resize(, LC)
        For x = LBound(arr, 2) To UBound(arr, 2)
            Application.FindFormat.Clear
            On Error Resume Next
            If arr(1, x) <> Empty Then
                Set rng = rngDate.Find(What:=Format(CDate(arr(1, x)), "Short Date"), After:=.Cells(7, 7), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)
                If Not rng Is Nothing Then
                    arr(1, x) = rng.Column
                Else
                    arr(1, x) = Empty
                End If
                Set rng = Nothing
            End If
            On Error GoTo 0
        Next x
    End With
    
    ColNum = arr
    
End Function

 

[JackDanIce]

Solution found..

Made two arrays of the original dates and then looped the sorted dates over the array of original dates and used their array index position to derive column index:

Private Function ColNum(ByRef arr As Variant, ByRef origDate As Variant, ByRef xRow As Long) As Variant


    Dim LC      As Long
    Dim x       As Long
    Dim i       As Long
        
    With Sheets("OR Sheet")
        LC = LastCol(Sheets("OR Sheet"), xRow) - 6
        
        For x = LBound(arr, 2) To UBound(arr, 2)
            For i = LBound(origDate, 2) To UBound(origDate, 2)
                If arr(1, x) = origDate(1, i) Then
                    arr(1, x) = i + 6
                    Exit For
                ElseIf arr(1, x) = Empty Then Exit For
                End If
            Next i
            If Not IsNumeric(arr(1, x)) Then arr(1, x) = Empty
        Next x
    End With
    
    ColNum = arr
    
End Function

There will never be more than 24 dates/columns so nested loop seems good a suggestion as any with minimal impact on execution time.

 

[Marcelo Branco]

Solution found..
Made two arrays of the original dates and then looped the sorted dates over the array of original dates and used their array index position to derive column index:
There will never be more than 24 dates/columns so nested loop seems good a suggestion as any with minimal impact on execution time.

Great, you found a solution! This is the more important.

Morning, based on Marcelo's function, this has same issue, in the CTRL+F Find dialogue box it's showing last searched value as 4/26/2017 but the array definitely contains 26/04/2017 with cell formatting and system settings set to UK style (DD/MM/YYYY)

But just FYI
Using Ctrl+F (in fact Ctrl+L in my Excel Brazilian version) i also see 4/26/2017 but my code works.
Unfortunately, i could not help much and find out why in your case it doesn't work.

M.

 

[JackDanIce]

Cheers Marcelo, it really is a strange bug.. you're right, a solution is found!