VBA replace function issue

Question

I have an issue with the REPLACE function of VBA. For example, whem I'm in a sheet and use CTRL + H to replace all the "." to ",", it works good. But when I'm trying to do it with a macro I have an issue with the values > than 1

0.8 ---> 0,8
0.9 ---> 0,9
1.09978063783 ---> 109978063783

Here is the code :

Rows("10:10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False, MatchByte:=False

Check this - https://stackoverflow.com/questions/23849437/excel-change-to — Vityata, Jun 09 '17 at 08:23
I don't want to change the setting, i have others macros working with "," as Decimal separator. — K.Fanedoul, Jun 09 '17 at 08:24
Excel evaluates it as separator so it converts it to whole number. What you can do is abandon `Replace` method and embrace looping. Or you can convert it to a function too and emulate the `Replace` method. — L42, Jun 09 '17 at 08:42
I tried it too, but i have so many data that Excel crash when i'm trying to loop through all the cells.. — K.Fanedoul, Jun 09 '17 at 08:44
I posted a messy solution which uses a separate procedure. I suggest you abandon the use of `Selection` to make sure you what we supply in the procedure is `Range` type object. — L42, Jun 09 '17 at 09:05
Have you tried to convert the value to `string`, do the replacement and then convert back to `double`? By the way, there's no need to use `Select` statement, you can put directly `Rows(10).Replace etc`. — PedroMVM, Jun 09 '17 at 12:33

score 0 · Answer 1 · answered Jun 09 '17 at 08:42

0

Would this do the trick for you?

Sub Test()
Dim i As Integer, rng As Range, cell As Range

For i = 10 To 1000
Set rng = Rows(i)
    rng.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False, MatchByte:=False
Next i
End Sub

answered Jun 09 '17 at 08:42

What's the issue with it? For me it replaces all dots with commas in the given rows. – Jun 09 '17 at 08:52
It works for values < than 1, if the value is > than 1 then it only delete the . and replace it with nothing – K.Fanedoul Jun 09 '17 at 08:56

score 0 · Answer 2 · answered Jun 09 '17 at 12:48

Try this code:

Sub DotsToCommas()
    Dim Txt As String, Col As Long
    For Col = 1 To ActiveSheet.Columns.Count
        Txt = CStr(ActiveSheet.Cells(10, Col).Value)
        If Txt = "" Then
            Exit Sub
        End If
        Txt = Replace(Txt, ".", ",")
        ActiveSheet.Cells(10, Col).Value = Txt
    Next
End Sub

It will take a bit more time, as it does a loop through all cells until it finds a empty one.

score 0 · Answer 3 · answered Jun 09 '17 at 12:57

This should do it:

Sub replaceDecimalPoint()

Dim lastRowNum As Long
Dim lastColNum As Long
Dim startRow As Long
Dim tempVal As String


lastRowNum = Cells(Rows.Count, 1).End(xlUp).Row
lastColNum = Cells(10, Columns.Count).End(xlToLeft).Column

startRow = 10 'Change this if row start number is required to be changed from 10

Dim rangeArray As Variant
rangeArray = Range(Cells(startRow, 1), Cells(lastRowNum, lastColNum)).Value 'Assuming to start at row 10 as you have used Row 10 (And Column A) - If NOT,     Change startRow

For r = startRow To lastRowNum 'Assuming to start at row 10 as you have used Row 10 - If NOT, Change startRow

    For c = 1 To lastColNum
        tempVal = rangeArray(r - (startRow - 1), c)
        If CDbl(tempVal) > 1 Then
            tempVal = CStr((tempVal * (10 ^ (Len(tempVal) - 1))))
        End If

        rangeArray(r - (startRow - 1), c) = Replace(CStr(tempVal), ".", ",")
    Next c

Next r

Range(Cells(startRow, 1), Cells(lastRowNum, lastColNum)).Value = rangeArray

End Sub

VBA replace function issue

3 Answers3