1

I need to change "." with "/" with a macro in Excel vba, it works, but when I activate the macro it changes the value of the cell from 10.11.2016 to 11/10/2016. Why does it change the order? The cell doesn't have any format.

Range("D1").Select
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
Masca2056
  • 13
  • 2
  • 1
    could you provide in your question the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve)." – Artem Sep 24 '18 at 09:47

1 Answers1

2

The cell doesn't have the format but it is mirroring the format from the regional settings. Check Control Panel | Clock and Region | Region. The moment you replace the text, it becomes a date and then it simply picks up the format from regional settings.

Try this

With Range("D1")
    .NumberFormat = "@"
    .Value = Replace(.Value, ".", "/")
End With

What I am doing here is converting the format of the cell to text and then replacing the characters.

Great, it works! But can I also replace "." with "/" in the entire column D with this method without using a loop? If yes how? Thank you – Masca2056 7 mins ago

Try this. I am assuming 10 is the last row.

Sub Sample()
    Columns(4).NumberFormat = "@"
    [D1:D10] = [INDEX(SUBSTITUTE(D1:D10,".","/"),)]
End Sub

See this LINK for an explanation of using the above method.

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Great, it works! But can I also replace "." with "/" in the entire column D with this method without using a loop? If yes how? Thank you – Masca2056 Sep 24 '18 at 10:34
  • You could replace `Range("D1")` with `Columns(4)` and `.Value = Replace(.Value, ".", "/")` with `.Replace ".", "/"` to do the whole column. **Edit**... but saying that, it brings back your original problem. – Darren Bartrup-Cook Sep 24 '18 at 10:43
  • @Masca2056 : I have edited the post. You may have to refresh the page to see it – Siddharth Rout Sep 24 '18 at 10:46