"Replace all" VBA macro

carlijn997

New Member
Joined
Aug 9, 2022
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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!

VBA Code:
Sub Macro1()

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

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
How about

VBA Code:
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
 
Upvote 0
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:

1660056190396.png

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) :

1660056497417.png


Thank you for your time!
 
Upvote 0
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:


VBA Code:
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:

VBA Code:
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:

1660057424107.png


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 :)
 
Upvote 0
You would need to cycle through the columns but Text to Columns might be an alternative:

VBA Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top