Macro replaces values in different cells, only first time used in sheet, how can i fix this?

I co-created a macro that toggle's between European and American data format. But when I run it for the first time in a file it replaces the value in to the first empty row from above. (when i convert the amount the whole column is selected).

This happens only the first time when the macro is ran in an new sheet. In the picture below I ran the macro 4 times, first column E, after, F,G and H:

I used the following code:

    Dim myRange As Range
    Set myRange = Selection

    myRange.Replace What:=".", Replacement:="ZZZZZ", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    myRange.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    myRange.Replace What:="ZZZZZ", Replacement:=",", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False

    myRange.NumberFormat = "#,###.00"
    myRange.TextToColumns Destination:=myRange, FieldInfo:=Array(1, xlGeneralFormat)

I expect it is caused by the .TextToColumns, but I use it to recognise as normal data format, otherwise it is still formatted as Text. Does anyone know how to solve this?

Thanks in advance, Dubblej

- - , .
.

Recent Questions...