Excel VBA Selection.replace do not replace everything

Question

Basically what I am trying to do is to imitate the replace button with VBA code. I use the following code:

Private Sub CommandButton1_Click()
    Blad1.Activate
    Blad1.Cells.Select
    Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

This replaces all of the dots, but in some cases it replaces the dots with whitespaces instead of commas. Why is this?

It works just fine for me. Can you give an example where it is not working? — Siddharth Rout
– Siddharth Rout, Commented Jun 13, 2012 at 7:48
Well, I have a lot of data, and it works fine in the first columns but when I come to column V and W it skips a lot of the cells. — Medo
– Medo, Commented Jun 13, 2012 at 8:02
would it be possible for me to see Col V/W data in a sample file? — Siddharth Rout
– Siddharth Rout, Commented Jun 13, 2012 at 8:02
Sure. Is it possible for me to send it too you through stackoverflow in anyway or do I need your email? — Medo
– Medo, Commented Jun 13, 2012 at 8:07
upload the file in www.wikisend.com and share the link here. — Siddharth Rout
– Siddharth Rout, Commented Jun 13, 2012 at 8:07

Siddharth Rout · Accepted Answer · 2012-06-13 08:48:16Z

2

Try this

Sub Sample()
    Dim oRange As Range, aCell As Range, bCell As Range
    Dim ws As Worksheet
    Dim ExitLoop As Boolean
    Dim SearchString As String, FoundAt As String
    On Error GoTo Err
    Set ws = Blad1
    Set oRange = ws.Cells

    oRange.NumberFormat = "@"

    SearchString = "."
    Set aCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
        Set bCell = aCell
        Do While InStr(1, aCell.Formula, ".") > 0
            aCell.Formula = Replace(aCell.Formula, ".", ",")
        Loop
        Do While ExitLoop = False
            Set aCell = oRange.FindNext(After:=aCell)

            If Not aCell Is Nothing Then
                If aCell.Address = bCell.Address Then Exit Do
                Do While InStr(1, aCell.Formula, ".") > 0
                    aCell.Formula = Replace(aCell.Formula, ".", ",")
                Loop
            Else
                ExitLoop = True
            End If
        Loop
    End If
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

answered Jun 13, 2012 at 8:48

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Medo Over a year ago

Thank you, that did work. I am guessing the thousands separator was responsible somehow, after really looking through the result I saw that only the cells with more than 4 numbers were troublesome.

Collectives™ on Stack Overflow

Excel VBA Selection.replace do not replace everything

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related