JackDanIce
Well-known Member
- Joined
- Feb 3, 2010
- Messages
- 9,922
- Office Version
- 365
- Platform
- Windows
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.
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
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.
Code:
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 FunctionNext, 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
