VBA code for find and replace keeps changing format

[Eurekaonide]

Hi All
I have the below code;

Range("W:W").Replace what:=".", replacement:="/"

It is just going down one column and replacing 01.12.2019 for example to 01/12/2019
but each time I run the code it randomly changes the format in some cells to date instead of text or general and then some of the dates get populated as 12.01.2019 which is a completely different date!

Ive tried setting the formats before and after, I e tried setting the formats in the replace code as true or false and the search formats but nothing stops it. Ironically if I do it manually its fine its just when its done via VBA.

Any suggestions please?

Thanks

 

[GWteB]

It's just the way Excel handles input with the characters / and - between numbers (provided there are no letters involved). Excel converts such numbers automatically to a date. When you want to prevent such behaviour, you can force Excel to handle the content of a cell as plain text by putting a single quotation mark in front of the content (01.12.2019 > '01.12.2019). In such a way you can manipulate that text as required.

Sub Replace_Example()
 
    Dim rngTmp      As Range
    Dim rngCell     As Range
    
    With ActiveSheet
        Set rngTmp = Intersect(.Range("A:A"), .UsedRange)
    End With
    For Each rngCell In rngTmp
        rngCell.Formula = "'" & rngCell.Formula
        rngCell.Replace what:=".", replacement:="/"
    Next rngCell
    
    Set rngTmp = Nothing
End Sub

 

[Eurekaonide]

Thank you so very much this works perfectly

 

[GWteB]

You're welcome & thanks for letting me know.