Excel VBA find and replace

excel, find, replace, vba

Solution

Is it what you are looking for ?

The below code will look for value in Column A of each sheet.

Sub ChgInfo()

    Dim WS As Worksheet
    Dim Search As String
    Dim Replacement As String
    Dim Prompt As String
    Dim Title As String
    Dim MatchCase As Boolean

    Prompt = "What is the original value you want to replace?"
    Title = "Search Value Input"
    Search = InputBox(Prompt, Title)

    Prompt = "What is the replacement value?"
    Title = "Search Value Input"
    Replacement = InputBox(Prompt, Title)

    For Each WS In Worksheets
        WS.Columns(1).Replace What:=Search, Replacement:=Replacement, LookAt:=xlPart, MatchCase:=False
    Next

End Sub

Updated Answer

Sub ChgInfo()

    Dim WS As Worksheet
    Dim Search As String
    Dim Replacement As String
    Dim Prompt As String
    Dim Title As String
    Dim MatchCase As Boolean
    Dim cell As Range
    Dim rngFind As Range
    Dim firstCell As String

    Prompt = "What is the original value you want to replace?"
    Title = "Search Value Input"
    Search = Trim(InputBox(Prompt, Title))

    Prompt = "What is the replacement value?"
    Title = "Search Value Input"
    Replacement = Trim(InputBox(Prompt, Title))

    For Each WS In Worksheets
        Set rngFind = WS.Columns(1).Find(What:=Search, LookIn:=xlValues, lookat:=xlPart)

        If Not rngFind Is Nothing Then firstCell = rngFind.Address

        Do While Not rngFind Is Nothing
            rngFind = Replacement & Mid(rngFind, 5, Len(rngFind))
            Set rngFind = WS.Columns(1).FindNext(After:=rngFind)
            If firstCell = rngFind.Address Then Exit Do
        Loop
    Next

End Sub

Problem

Original source