find and replace values in database using an array VBA

Question

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

What don't you understand? Have you tried to run it? What does it do or not do that it should do or shouldn't do? — nicomp, Feb 03 '17 at 16:00
This code has nothing in it about a database. Do you mean an excel sheet? — Cody Ｇ, Feb 03 '17 at 16:01
Please read [How to Ask](http://stackoverflow.com/help/how-to-ask) and edit your question accordingly to receive the most effective help. Reading that may assist in your understanding that, as written, this question is almost impossible to answer in any meaningful way. — Scott Holtzman, Feb 03 '17 at 16:06
Sorry if the question was badly explained: I´m very new at this. I have just edited my previous message with an update on the code, its still not working. The error arises when the for loop starts. I actually just need it to loop through a range, not the whole worksheet. Cody G, you are right its not a database, only a spreadsheet — Urumita, Feb 03 '17 at 16:17
is it a range or an Excel table as the code refers to a table (listobject in Excel speak)? Edit - sorry, misunderstood I think. You want to replace values in a range based on values in a two column table? — SJR, Feb 03 '17 at 16:30

TheSilkCode · Answer 1 · 2017-02-03T16:41:11.440

I have adjusted your code which you can see below; couple notes:

1- Using Option Explicit is always a good idea 2- If you put the array loop inside the sheet loop, you only have to perform the sheet name check n times (n=number of sheets in workbook), if you put the sheet loop inside the array loop you would have to perform the sheet name check n*x times (x = number of items in your array)... 3- You didn't specify, but I assumed that your Table1 was structured vertically with the lookup value in the first column and the replacement value in the 2nd- so there is no need to transpose your array; if your Table1 is in fact horizontal then you would need to adjust this code...

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

Hope this helps, TheSilkCode

*Using Option Explicit is always a good idea* - However, you show no reference to this either in your code or in a link :( — Scott Holtzman, Feb 03 '17 at 16:40
Hi @ScottHoltzman , Type 'Option Explicit' at the top of your module to enable it... See link on it here: [How do I force VBA/Access to require variables to be defined?](http://stackoverflow.com/questions/1139321/how-do-i-force-vba-access-to-require-variables-to-be-defined) — TheSilkCode, Feb 03 '17 at 16:44

score 0 · Accepted Answer · answered Feb 03 '17 at 18:21

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

Hope this helps, TheSilkCode

score 0 · Answer 3 · answered Sep 21 '18 at 14:11

Using a slight adjustment of TheSilkCode code you could loop through a worksheet as follows:

Option Explicit

Public Sub pDemo()
    Dim vMappingTable() As Variant
    Dim rowCounter As Long

    '1) Create an Array out of the Old to New Name mapping
    vMappingTable = wksMappings.ListObjects("tbl_Mapping").DataBodyRange

    '2) Loops through desired sheet and replaces any cells that contain the first column val, with the 2nd column val...
    With wksToReplace.Range("X:X")
        For rowCounter = LBound(vMappingTable, 1) To UBound(vMappingTable, 1)
            .Cells.Replace What:=vMappingTable(rowCounter, 1), Replacement:=vMappingTable(rowCounter, 2), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Next
    End With

End Sub

Note: you can define names of table via the Name manager (Ctrl+F3) and you can set the name of worksheets in your project in the properties in the VBA editor which I have done here or use the default names/and or path.

find and replace values in database using an array VBA

3 Answers3

Linked