使用数组 VBA 查找并替换数据库中的值

我有一个脏数据库，每个人的名字都以不同的方式写入，我无法将它们分组。

我想创建一个宏来使用两列列表查找和替换数据库中的名称。

我找到了以下代码，但我无法理解它，因此无法修改它：

Dim Sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant
Dim Rng As Range


'Create variable to point to your table
  Set tbl = Worksheets("How to").ListObjects("Table2")

'Create an Array out of the Table's Data
  Set TempArray = tbl.DataBodyRange
  myArray = Application.Transpose(TempArray)

'Designate Columns for Find/Replace data
  fndList = 1
  rplcList = 2

'Loop through each item in Array lists
  For x = LBound(myArray, 1) To UBound(myArray, 2)
    'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
      For Each Rng In Worksheets("xxxxxxxxxx").Activate
        If Rng.Name <> tbl.Parent.Name Then

          Rng.Cells.replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False

        End If
      Next Rng

  Next x

End Sub

我已经调整了您的代码，您可以在下面看到；夫妇注意事项：

1-使用 Option Explicit 总是一个好主意 2-如果将数组循环放入工作表循环中，则只需执行工作表名称检查n次（n=工作簿中的工作表数量），如果将工作表循环放入数组循环中，则必须执行工作表名称检查 n*x 次（x = 数组中的项目数）... 3-您没有指定，但我假设您的 Table1 是垂直结构的，查找值位于第一列，替换值位于第二列-因此无需转置数组；如果您的 Table1 实际上是水平的，那么您需要调整此代码......

Public Sub demoCode()
Dim sheetName As String
Dim tableRange As Range
Dim myArray() As Variant
Dim wsCounter As Long
Dim rowCounter As Long

'Store name of sheet with lookup table
sheetName = "How to"

'Create an Array out of the Table's Data
Set tableRange = ThisWorkbook.Sheets(sheetName).ListObjects("Table1").DataBodyRange
myArray = tableRange

'Loop through each sheet
For wsCounter = 1 To ThisWorkbook.Sheets.Count
    With ThisWorkbook.Sheets(wsCounter)
        'Test to make sure the sheet is not the sheet with the lookup table
        If .Name <> sheetName Then
            'Loop through each item in lookup table
            For rowCounter = LBound(myArray, 1) To UBound(myArray, 1)
                'Replace any cells that contain whats in the first column of the lookup table, with whats in the 2nd column..
                .Cells.Replace What:=myArray(rowCounter, 1), Replacement:=myArray(rowCounter, 2), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            Next
        End If
    End With
Next

End Sub

希望这可以帮助， 丝绸密码