CODEWITHSUNDEEP
excelvba
TAH.
TAH.

Reputation: 11

Replace dot with comma, and comma with dot

I am trying to replace dots with commas and commas with dots. The data is not always the same length. I created a button (Form controls) for the code.

I tried writing the code for replacing dots with commas. The code works for replacing dots with commas. However Excel gets stuck. How do I add the part to replace commas with dots?

Sub ReplacePointWithComma()

Dim Cell As Range

ActiveSheet.Range("D:I").Select

For Each Cell In Selection
    Cell.Value = Replace(Cell.Value, ",", ".")
Next

End Sub

Upvotes: 0

Views: 1285

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

Replace like you're using, is VBA's VBA.Strings.Replace function. That works, but as you've seen, when iterating 6 million individual cells, it takes a while.

Consider using Range.Replace instead, as Scott showed in a comment above - that way you don't need to iterate anything; this should be much, much faster:

With ActiveSheet.Range("D:I")
    .Replace ",", "|" ' need a temp placeholder character, otherwise we'd end up with only commas!
    .Replace ".", ","
    .Replace "|", "."
End With

Upvotes: 5

JNevill
JNevill

Reputation: 50308

You'll have to make three chances.

  1. Change commas to something that is unique in your cell
  2. Change dots to commas
  3. Change that unique character to dots:

You can do this with one line of code where the inner most Replace is step 1, the second most inner is step 2 and then outside Replace is step 3. 

Sub ReplacePointWithComma()

Dim Cell As Range

ActiveSheet.Range("D:I").Select

For Each Cell In Selection
    Cell.Value = Replace(Replace(Replace(Cell.Value, ",", "|"), ".", ","), "|", ".")
Next

End Sub

One other suggestion here is to not use .Select or .Activate. Humans select and activate cells and sheets, but VBA has no need for that. Just tell it which cells to act on:

Sub ReplacePointWithComma()

Dim Cell As Range

For Each Cell In ActiveSheet.Range("D:I").Cells
    Cell.Value = Replace(Replace(Replace(Cell.Value, ",", "|"), ".", ","), "|", ".")
Next

End Sub

Lastly, recognize that this is going to go through EVERY cell in columns D through I. Thats going to be 6 million iterations. Excel is doing it, but it's going to take forever. Instead give it a reasonable range to iterate through:

Sub ReplacePointWithComma()

Dim Cell As Range

For Each Cell In ActiveSheet.Range("D1:I10000").Cells
    Cell.Value = Replace(Replace(Replace(Cell.Value, ",", "|"), ".", ","), "|", ".")
Next

End Sub

Upvotes: 0

Related Questions