Excel VBA 查找和替换

Excel VBA find and replace

提问by Roberto Bahia

I have this code and it works fine. Now I need change it and I don't know how do it.

我有这个代码，它工作正常。现在我需要改变它，我不知道怎么做。

The code searsh in entire wordksheet. I need search in only 1 column

代码在整个 wordksheet 中搜索。我只需要在 1 列中搜索

the code search entire cell. I need search left or mid or right side of cell.

代码搜索整个单元格。我需要搜索单元格的左侧或中间或右侧。

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.Cells.Replace What:=Search, Replacement:=Replacement, _ 
LookAt:=xlPart, MatchCase:=False 
Next 

End Sub

回答by Santosh

Is it what you are looking for ?

这是您要找的吗？

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

下面的代码将在每个工作表的 A 列中查找值。

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

回答by Deividas

I used this to replace something somewhere.It's simple but works good for me

我用它来替换某处的东西。这很简单，但对我很有用

   Sub test()
Dim x As String, y As String
y = InputBox("Replace what?")
x = InputBox("Replace to?")
    [m12:m20,I2,O7,P5,P6].Replace what:=y, replacement:=x
 End Sub