Replacing commas with dots in Excel script

Question

I want to replace the comma with a dot and vice versa

Public Sub VirgulaPunct() Dim oRow As Range Dim cell As Range Dim i As Long, j As Long Dim MyString As String Dim aux As String Application.ScreenUpdating = False For i = Selection(Selection.Count).Row To Selection.Cells(1, 1).Row Step -1 For j = Selection(Selection.Count).Column To Selection.Cells(1, 1).Column Step -1 MyString = Cells(i, j).Value For Counter = 1 To Len(MyString) aux = Mid(MyString, Counter, 1) If aux = "." Then MyString = Replace(MyString, ".", ",", 1) ElseIf aux = "," Then MyString = Replace(MyString, ",", ".", 1) End If Next Cells(i, j).Value = MyString Next j Next i Application.ScreenUpdating = True End Sub 

Answer 1

You can use the Replace method of a Range object

 Sub ReplacePunct() Const sTEMPCOMMA = "|comma|" Const sTEMPDOT = "|dot|" Const sCOMMA = "," Const sDOT = "." If TypeName(Selection) = "Range" Then With Selection .Replace sCOMMA, sTEMPCOMMA, xlPart .Replace sDOT, sTEMPDOT, xlPart .Replace sTEMPCOMMA, sDOT, xlPart .Replace sTEMPDOT, sCOMMA, xlPart End With End If End Sub 

Answer 2

The main problem is that if the columns are set as general, not text, even if we change "," to ".". Excell will automatically change it again to ",".

To avoid this, you must first set the column as a text format, and then we can perform the replacement function:

This works for me:

 Worksheets("Name").Activate Worksheets("Name").Columns(1).Select 'or Worksheets("SheetName").Range("A:A").Select Selection.NumberFormat = "@" Dim OriginalText As String Dim CorrectedText As String LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row For i = 1 To LastRow OriginalText = Worksheets("Name").Cells(i, 1).Value CorrectedText = Replace(OriginalText, ",", ".") Worksheets("Name").Cells(i, 1).Value = CorrectedText Next i 

Answer 3

Looking at your hinges, you don’t see that you visit any camera more than once. Wouldn't there be a simple if statement here?

 if(Cells(i,j).Value = ',') Cells(i,j).Value = '.' Elsif(Cells(i,j).Value = '.') Cells(i,j).Value = ',') End 

Answer 4

From the perspective of VB, not VBA, I would look at using the built-in Replace function, not at it.

So first, I would replace everything with ; (or, if the text can be ; I would use something else, maybe a combination of characters, for example ;+;+ or the like) then replace everything . on , and finally replace everything ; on . .
Probably not very effective, but very simple.

Answer 5

This seems to be a continuation of my answer to your previous question, but if so, I think you misunderstood what I had in mind. I accepted your code and amended my suggestion, but I have not tested it:

 Public Sub VirgulaPunct() Dim oRow As Range Dim cell As Range Dim i As Long, j As Long Dim MyString As String Dim aux As String Application.ScreenUpdating = False For i = Selection(Selection.Count).Row To Selection.Cells(1, 1).Row Step -1 For j = Selection(Selection.Count).Column To Selection.Cells(1, 1).Column Step -1 MyString = Cells(i, j).Value MyString = Replace(MyString, ",", ";+;", 1) MyString = Replace(MyString, ".", ",", 1) MyString = Replace(MyString, ";+;", ".", 1) Cells(i, j).Value = MyString Next j Next i Application.ScreenUpdating = True End Sub 

So, as I said in my previous answer, I make 3 calls to Replace , but I make them for the whole line, not for the character in the line.

In the future, it would probably be better if you updated the original question rather than created a new one, and then you could leave a comment for me under my answer, and I saw that you did.