Excel VBA 查找和替换

Question

我有这段代码，它可以正常运行。现在我需要对其进行修改，但我不知道如何操作。

该代码在整个工作表中搜索。我需要仅在1列中搜索。

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

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

- Roberto Bahia

---

你说的“代码搜索整个单元格。我需要搜索单元格左侧、中间或右侧。”是什么意思？ - Santosh

WS.Columns(1).Replace What:=Search, Replacement:=Replacement, LookAt:=xlPart, MatchCase:=False看起来你的代码已经可以替换单元格内容的部分，而不仅仅是整个文本。除非你指的是“左侧或中间或右侧”其他的东西。 - Tim Williams

Answer 1

您是否正在寻找这个？

以下代码将在每个工作表的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

更新回答

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

- Santosh

---

谢谢Santosh，现在已经完成50％了，现在我需要在单元格的前4个左字符中找到“only”。 - Roberto Bahia

例如，单元格内容为：united-united，我需要找到字符unit并替换为aaaa，结果为：aaaaed-united。 - Roberto Bahia

@RobertoBahia 感谢您提供的示例。请给我几分钟。 - Santosh

好的。另一个例子是：单元格内容为santoshcoolman，查找最后一个字符中的A并替换为E，那么单元格内容为santoscoolmEn，保留第一个A。 - Roberto Bahia

对我来说没问题。如果你有其他的代码，我可以改变所有的代码。 - Roberto Bahia

Answer 2

我用这个替换了某个地方的东西。它很简单，但对我来说很有效。

   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   

- Deividas