"Replace all" VBA macro

[carlijn997]

Hi!

I'm very new with VBA so I mostly use the 'record macro' function, to then learn from the code and combine pieces I want for a macro.

I am European so we use a comma for decimals and not dots. Many, but not all files I work with have dots though, and my European version of Excel cannot calculate with them, since it does not recognise it as a number, but as text. I normally can fix this very easily by using "replace all", replacing all dots with comma's. When I record this in a macro, it works normally, but when i try to use that recorded macro, it just deletes the dot completely, leaving me with the numbers without a comma or a dot (e.g. "1.23456" becomes "123456" and not "1,23456" like I want). It does it to all numbers, regardless of how many decimals it has, so just deviding every number is also not an option. Any idea why this happens and how I can fix it or a different code I can use to do what I am trying to do? I need to repeat this very often, together with some simple calculations, hence me trying to make a macro. Thanks!

Sub Macro1()

    Cells.Replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder _
        :=xlByRows
        
End Sub

 

[nemmi69]

How about

Sub EuroConvert()
Dim Test As String
Dim Euro As String
Dim US As String

Test = "4.321,01"

US = Euro2US(Test)
Euro = US2Euro(US)


MsgBox Euro & " : " & US

End Sub


Function Euro2US(Orig As String) As String
Dim Eloop As Long
Dim TStr As String

TStr = ""
For Eloop = 1 To Len(Orig)
    If Mid(Orig, Eloop, 1) = "." Then
        TStr = TStr & ","
    ElseIf Mid(Orig, Eloop, 1) = "," Then
        TStr = TStr & "."
    Else
        TStr = TStr & Mid(Orig, Eloop, 1)
    End If
Next Eloop
Euro2US = TStr
End Function

Function US2Euro(Orig As String) As String
Dim Eloop As Long
Dim TStr As String

TStr = ""
For Eloop = 1 To Len(Orig)
    If Mid(Orig, Eloop, 1) = "." Then
        TStr = TStr & ","
    ElseIf Mid(Orig, Eloop, 1) = "," Then
        TStr = TStr & "."
    Else
        TStr = TStr & Mid(Orig, Eloop, 1)
    End If
Next Eloop
US2Euro = TStr
End Function

 

[HaHoBe]

Hi carlijn997,

maybe the description and code in Replacing Commas with Periods may help.

Ciao,
Holger

 

[carlijn997]

I am very glad about the fast reply! But like I said I am very new at this, so for me it is quite complicated to understand the code you just shared. What is it supposed yo do? And how do I use of it to do what I need? Right now if I copy it and run this it comes back with:

Which seems like it works, so that's great! How do I use this to change the values is my worksheet?

If it helps this is what all workbooks look like, that I am trying to replace the dots in (sometimes there is also values in Column C) :

Thank you for your time!

 

[carlijn997]

Hi carlijn997,

maybe the description and code in Replacing Commas with Periods may help.

Ciao,
Holger

Thank you! I could be that I don't know what I am doing, or this is not what I am looking for. I tried both the codes mentioned, but it results with the following:

Sub ChangeNumberFormat()
    With Application
        .UseSystemSeparators = False
        If .DecimalSeparator = "." Then
            .DecimalSeparator = ","
            .ThousandsSeparator = "."
        Else
           .DecimalSeparator = "."
           .ThousandsSeparator = ","
        End If
    End With
End Sub

This code above changes nothing at all? And the next:

Sub SwitchCommasPeriods()
    Dim c As Range
    Dim sTemp As String
    
    For Each c In Selection
        sTemp = c.Text
        sTemp = Replace(sTemp, ",", "|")
        sTemp = Replace(sTemp, ".", ",")
        sTemp = Replace(sTemp, "|", ".")
        c = sTemp
    Next c
End Sub

this code results in the following:

As you can see, it changes the visual, but still just deletes the dot, removing all meaning of the decimals.

Sorry if I am just not understanding this or getting it wrong. If needed I will just stick with my "Replace all" haha, but still hoping someone can help

 

[Alex Blakenburg]

You would need to cycle through the columns but Text to Columns might be an alternative:

With Columns(2)
    .TextToColumns Destination:=Columns(2), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), DecimalSeparator:=".", ThousandsSeparator:=",", _
        TrailingMinusNumbers:=True
End With

 

[HaHoBe]

Hi carlijn997,

looking at the Formula Bar in your picture I can only guess that the display of the number in B2 may be changed via CTRL+1 to a "normal" number. And the logic of always having a minus and 2 digits before the comma may be resolved either by formula or VBA. At least that´s my guess from the picture.

Ciao,
Holger