Sunday, February 15, 2009

Excel VBA: How To Replace Data Using Excel VBA Macro Code

In my case, I often manipulating data, including but not limited to searches all data in a worksheet and makes the specified replacement.

Here's an example how replace data using excel vba macro code. This example will replace all cell value that contain word "micro excel" to "macro excel" in active sheet.

Sub ReplaceAllData()

    Cells.Replace _
        What:="micro excel", Replacement:="macro excel", _
    LookAt:=xlWhole, MatchCase:=False

End Sub

If you to make the search case sensitive you can change the MatchCase property to true. And also if you want to replace data that contain part of the searched data you can change the LookAt property to xlPart.

Related posts:
If you like posts in this blog, you can to support me :)

1 comment:

  1. How can I ask excel to replace text Jul17 to Aug17 and leave the cell format as text. My Cell.Replace is changing the cell format from text to Custom "d-mmm". Instead of the result being Aug17 it is changing it to 17-Aug