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

 

[Norie]

Jack

How are you populating the array?

Also, have you tried either CLng, CDate or DateValue with either method?

 

[JackDanIce]

@Norie I'm populating the array like:

arr = Cells(x, 7).Resize(, LC).Value

and tried using .Value2 but no joy
I have tried both CLng and CDate but same outcome. I haven't tried DateValue which was next to try

@Marcelo jumping the gun (i.e. before trying), you're using virtually the same line I had:

Set rFound = rngDate.Find(arr(1, x), , xlValues, xlWhole, xlByColumns)

where mine was

arr(1, x) = rngDate.Find(arr(1, x), , xlValues, xlWhole, xlbyColumns).Column

where Dim arr() as Variant set earlier and for the function argument I'm using byref arr() as variant (instead of byref arr as variant)

At that point, (using F5 to step through), I can see arr(x, 1) contains #26/04/2017# yet immediately after, it contains error and CTRL+F on spreadsheet brings up Find dialogue box showing 4/26/2017 which suggests to me it was the last value that was found, but I did not enter this value in or use CTRL+F prior to stepping through the code or before I'd run the code entirely and discovered this issue.

I will try tomorrow, but as far as I can tell the rest of the code does test or correct for data types...

 

[Norie]

Jack

You've found one of the problems with Find.

The default arguments of Find are based on the last search executed, whether it was in code or manually, so unless you specify values for all the arguments of Find you might not get the expected results.

Try turning on the macro recorder, perform a succesful search and then substitute your Find code with that from the generated code.

 

[JackDanIce]

That is an unusual interesting quirk, that search item must have been left from an earlier run and then I did make adjustments to the code including not using the full .Find function with all arguments specified.

Cool, more optimistic will get the work completed sooner, thank you, much appreciated! Hope this isn't false optimism for tomorrow...!

 

[Marcelo Branco]

@Marcelo jumping the gun (i.e. before trying), you're using virtually the same line I had:

Set rFound = rngDate.Find(arr(1, x), , xlValues, xlWhole, xlByColumns)

where mine was

arr(1, x) = rngDate.Find(arr(1, x), , xlValues, xlWhole, xlbyColumns).Column

Don't jump the gun... Try it...
Yes, i used your code as a base - tried to change it as little as possible.
As I said, it worked for me (dates dd/mm/yyyy) - so, unless there's something weird, which I cannot imagine, i think it should work for you too

At that point, (using F5 to step through), I can see arr(x, 1) contains #26/04/2017# yet immediately after, it contains error and CTRL+F on spreadsheet brings up Find dialogue box showing 4/26/2017 which suggests to me it was the last value that was found, but I did not enter this value in or use CTRL+F prior to stepping through the code or before I'd run the code entirely and discovered this issue.

Are you saying the dates are wrapped by hashtags? Is it?

M.

 

[JackDanIce]

The intermediate window and hovering over the element both show it as #26/04/2017# and cell reads as 26/04/2017 with format set to DD/MM/YYYY but will try tomorrow, thank you

 

[Norie]

Marcelo

Which date format do you use?

 

[Marcelo Branco]

Marcelo

Which date format do you use?

dd/mm/yyyy

 

[Marcelo Branco]

The intermediate window and hovering over the element both show it as #26/04/2017# and cell reads as 26/04/2017 with format set to DD/MM/YYYY but will try tomorrow, thank you

If so, it seems that you used
Dim arr as Date
didn't work for me too

try
Dim arr as Variant

M.

 

[JackDanIce]

Nope did declare as variant!