[SOLVED] Date with and without spaces between dots

Date with and without spaces between dots

I was facing with a type mismatch error in VBA and I have finally found the root cause but I don't know how to solve it.

The issue is if I call the Date function in VBA on my notebook, I get in the following format: "2023.04.25" (without spaces). If I do the same on my colleagues' notebooks, I get the date with spaces between the dots, like this: "2023. 04. 25". That's why the macro runs correctly on my notebook and show type mismatch error on my colleagues' notebooks. Any idea how to solve it? Can it be a basic setting or something like this?

            y1 = 3
            
            While IsEmpty(Cells(y1, 1)) = False
            Cells(y1, 1) = Replace(Cells(y1, 1), " ", "")
            Cells(y1, 1) = CDate(Cells(y1, 1))      <--- type mismatch error
            y1 = y1 + 1
            Wend

In the cells (y1,1) are dates and I wanted to check is there any space in the date or not. If yes, delete it. Everything was fine on my notebook, but I got type mismatch error on other notebook with the same VBA, same dates.

I tested the dates with the following code (dates from A3 to A15):

sub
Dim A As String
Dim B As Date
Dim i As Variant

For i = 3 To 15
    A = Cells(i, 1)
    B = CDate(A)
    Cells(i, 3) = B
Next
end sub

With this everything is fine on each notebook. If I use the replace command to delete spaces from the date cells, I got again type mismatch error.

Dates were copied from an excel file which was imported from SAP. Example: 2023.04.03.

Thank you in advance!

Solution

Apply Replace twice:

While IsEmpty(Cells(y1, 1)) = False
    Cells(y1, 1) = CDate(Replace(Replace(Cells(y1, 1), " ", ""), ".", "-"))
    y1 = y1 + 1
Wend