Excel Find and Replace does not work correctly when changing numbers formatted as text

Question

Windows 7

Office 2013

I have a spreadsheet that uses a cell as an ID. The ID can be text or numbers so the cells are formatted as text.

This works ok.

I have Excel VBA that does a find and replace and it works except when I try to find and replace a number.

If I find 1 and replace it with 10.00, the cell is changed to 10,  not 10.00. The cell is formatted as text.

Some of the VBA code

TaskID and NewTaskID are defined as String

Selection.Replace What:=TaskID, Replacement:=NewTaskID, LookAt:=xlWhole, _

                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

                ReplaceFormat:=False

I have tried

"'" & NewTaskID. This works but the cells have a prefix of '. This is ok until another find and replace is run and another prefix of '.

Then I get ''10

I have tried CStr(NewTaskID)

I have tried setting the ReplaceFormat to Number = Text and changing ReplaceFormat to True.

I have tried to use the Find and Replace in Excel, not in VBA, to try and resolve the problem and then I could use a macro to get the VBA code, but Find and Replace has the same issue. No matter what I do, if I replace 10.00 the cell is set to 10 unless I put an '.

I have tried the ReplaceFormat and set that to Number = Text but that doesn't work.

Any ideas please ?

Thank you

Answer 1

If there are multiple changes, it doesn't find and change them.

I didn't realize that you were doing multiple changes but no problem. Try the following where it loops until the value is no longer found in the selection.

If you have very many changes to make in a large amount of data then the method is certainly slower but unfortunately we have to run with the work around until such time as Microsoft addresses the issue (if they ever do so).

Sub FindAndReplaceTextNumbers()

    Dim TaskID As String

    Dim NewTaskID As String

    Dim rngToFind As Range

    TaskID = Range("A9").Value      'Used for testing

    NewTaskID = "01012"             'Used for testing

    Columns("A:A").Select           'Used for testing

    Set rngToFind = Selection.Find(What:=TaskID, _

                LookIn:=xlFormulas, _

                LookAt:=xlWhole, _

                SearchOrder:=xlByRows, _

                SearchDirection:=xlNext, _

                MatchCase:=False, _

                SearchFormat:=False)

    'Test if found because if not found then code will error

    If Not rngToFind Is Nothing Then    'Not nothing then is something so found

        Do

            rngToFind.Value = NewTaskID

            Set rngToFind = Selection.FindNext(rngToFind)

        Loop While Not rngToFind Is Nothing

    End If

End Sub

Answer 2

Thank you for the reply

I have tried that but it only changes one cell. If there are multiple changes, it doesn't find and change them.

Answer 3

Yes, it is a pain when it does not work as one would expect. However, the workaround is to perform the operation in 2 steps. Find first and if found then replace.

Also

Sub FindAndReplaceTextNumbers()

    Dim TaskID As String

    Dim NewTaskID As String

    Dim rngToFind As Range

    TaskID = Range("A9").Value      'Used for testing

    NewTaskID = "01012"             'Used for testing

    Columns("A:A").Select           'Used for testing

    Set rngToFind = Selection.Find(What:=TaskID, _

                LookIn:=xlFormulas, _

                LookAt:=xlWhole, _

                SearchOrder:=xlByRows, _

                SearchDirection:=xlNext, _

                MatchCase:=False, _

                SearchFormat:=False)

    'Test if found because if not found then code will error

    If Not rngToFind Is Nothing Then    'Not nothing then is something so found

        rngToFind.Value = NewTaskID

    End If

End Sub