使用数组VBA查找和替换数据库中的值 [英] find and replace values in database using an array VBA

问题描述

我有一个肮脏的数据库，其中每个人的姓名以不同的方式书写，我无法对它们进行分组.

I have a dirty database where the names of each individual are written in different ways and I cannot group them.

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

I would like to create a macro to find and replace the names in the database using a two column list.

我找到了以下代码，但是我在理解它时遇到了麻烦，因此无法适应它:

I have found the following code, but I´m having trouble understanding it, so cannot adapt it:

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

推荐答案

所以要回答第二个问题，基本上，您需要做的是先删除工作表循环(已完成)，然后删除要处理的部分缺少的是，您还需要指定您希望代码仅对目标范围内的单元格执行替换，而不是对工作表中的单元格(将是所有单元格)执行替换...请参见以下示例:

so to answer your second question, basically what you would need to do is remove the sheet loop (which you have done), and then the part you're missing is you also need to specify you want the code to perform the replace on just the cells within the target range, instead of performing it on the cells within the sheet (which would be all the cells)... see below for example:

Public Sub demoCode_v2()
Dim tableRange As Range
Dim myArray() As Variant
Dim rowCounter As Long
Dim targetRange As Range

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

'Select target range
Set targetRange = Application.InputBox("Select target range:", Type:=8)

'Loop through each item in lookup table
For rowCounter = LBound(myArray, 1) To UBound(myArray, 1)
    'Replace any cells in target range that contain whats in the first column of the lookup table, with whats in the 2nd column..
    targetRange.Cells.Replace What:=myArray(rowCounter, 1), Replacement:=myArray(rowCounter, 2), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next

End Sub

希望这会有所帮助，TheSilkCode

Hope this helps, TheSilkCode

这篇关于使用数组VBA查找和替换数据库中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！