Date changes to US format when using VBA

[hocohen]

The issue is:

1. I export SAP report to Excel.
2. One of the fields is a Date in Text format "dd.mm.yyyy"
3. In order to convert it to a Date format - I replace the "." into "/", and format the field as date "dd.mm.yyyy".
4. If I do the replacement in Excel, the result is correct "dd/mm/yyyy", but If I do it in VBA - the day and month alternate and the result is "mm/dd/yyyy".

I understand it happens because the VBA format is always US.
The question is if there is a solution for that.

 

[dmt32]

Macro stopped at:
For i = 1 To UBound(va, 1)
Type mismatch

untested but see if this update to @Akuini suggested array code resolves the issue

Sub tryAgain()
    Dim arr                       As Variant
    Dim rng                      As Range
  
    '------------------------------------------------------------------------------------------------------------------------
    '                                                       SETTINGS
    '-----------------------------------------------------------------------------------------------------------------------
    Const DateColumn    As Long = 1                   '<< 'change as required
    Const DateFormat     As String = "dd-mm-yyyy"     '<<
    '-----------------------------------------------------------------------------------------------------------------------
  
    'size the date column range
    Set rng = Cells(2, DateColumn).Resize(Cells(Rows.Count, DateColumn).End(xlUp).Row - 1)
  
    'intialize array
    arr = rng.Value
  
    For i = 1 To UBound(arr, 1)
       If arr(i, 1) Like "##.##.####" Then
            arr(i, 1) = CDate(Replace(arr(i, 1), ".", "/"))
       End If
    Next
  
    'format range
    rng.NumberFormat = DateFormat
    'post array to range
    rng = arr
  
End Sub

Dave

 

[hocohen]

Joe4​

Compile error on line:
For Each cell In Range("A2:A" & lr)
"For Each control variable must be Variant or Object"

 

[Joe4]

I originally had a typo in my code which I fixed yesterday.
Maybe you tried it out before the fix.

Try copying the code from that post now, and see if it works.