☰

VBA 宏查找和替换

tag-icon • tag-icon microsoft-excel vba macros find replacement
VBA 宏查找和替换

我正在寻找制作一个宏的方法，该宏使用查找和替换功能，使用两列表格（列 A 和列 B）中的数据，将工作簿其余部分中包含列 A 数据的所有内容替换为列 B 中同一行的数据。

我有下面这个宏的代码可以执行此操作，但现在它只会更改 C 列中的数据。编辑此宏以将编辑范围从 C 列更改为工作簿中的其他每个工作表有多困难？

任何帮助都将不胜感激。谢谢。

当前代码如下：

Public Sub MyReplace()
' Define variables '
Dim sh As Worksheet
Dim dst As Range
Dim vals() As Variant
Dim i As Integer
' Reference to a sheet '
Set sh = ThisWorkbook.Worksheets("Sheet1")
' Reference to the range where to replace (column C) '
Set dst = sh.Range(sh.Cells(1, 3), _
                   sh.Cells(sh.UsedRange.Row + sh.UsedRange.Rows.Count - 1, 3))
' Get the replacement values pairs (from columns A and B) '
vals = sh.Range(sh.Cells(1, 1), _
                sh.Cells(sh.UsedRange.Row + sh.UsedRange.Rows.Count - 1, 2)).Value
' process each replacement values pair '
For i = LBound(vals) To UBound(vals)
    ' If both values are not empty '
    If Len(Trim(vals(i, 1))) > 0 And Len(Trim(vals(i, 2))) > 0 Then
        ' then perform the replacement '
        dst.Replace What:=Trim(vals(i, 1)), Replacement:=Trim(vals(i, 2)), _
                    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                    SearchFormat:=False, ReplaceFormat:=False
    End If
Next
End Sub

答案1

请尝试以下步骤，然后让我知道这是否是您想要的（或者它是否有效），它适用于我的数据，但我不知道您使用的格式...我认为这就是您想要的。

Option Explicit

Public Sub MyReplace()
' Define variables '
Dim sh As Worksheet
Dim sh1 As Worksheet: Set sh1 = Sheet1
Dim dst As Range
Dim vals() As Variant
Dim i As Integer

For Each sh In Worksheets
    If sh.Name <> Sheet1.Name Then
        vals = sh.UsedRange.Value
        Set dst = sh.UsedRange
        MsgBox UBound(vals)
        ' process each replacement values pair '
        For i = LBound(vals) To UBound(vals)
            ' If both values are not empty '
            If Len(Trim(sh1.Cells(i, 1))) > 0 And Len(Trim(sh1.Cells(i, 2))) > 0 Then
                ' then perform the replacement '
                dst.Replace What:=Trim(sh1.Cells(i, 1)), replacement:=Trim(sh1.Cells(i, 2)), _
                                LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                                SearchFormat:=False, ReplaceFormat:=False
            End If
        Next i
    End If
Next sh
End Sub

相关内容