find and replace values in excel using vba - lookup table overwritten

Question

I am using basically the solution from this question find and replace values in database using an array VBA and it also works just fine. However, since a couple of days, when executing the code also replaces the lookup values in the left column of the table array and I don't know why this is happening. The lookup table is called tab_replace on the tab_replace worksheet. So, whenever the code executes the replacement on the target sheet, the value in the first column of the lookup table is also replaced.

Sub Datastream_Code_Replacement()

Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant
Dim TempArray As Variant
Dim targetRange As Range
Dim X As Integer

Application.DisplayAlerts = False

  Set tbl = Worksheets("tab_replace").ListObjects("tab_replace")

  Set TempArray = tbl.DataBodyRange
  myArray = TempArray
  

  fndList = 1
  rplcList = 2

'Loop through each item in Array lists

    'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
    ' Skip the request table, so that no Reuters Codes are replaced
      For Each sht In ActiveWorkbook.Worksheets
        If sht.Name <> "CodeReplacement" Then
        If sht.Name <> "tab_replace" Then
        If sht.Name <> "REQUEST_TABLE" Then
        If sht.Name <> "Hilfsfunktionen" Then
       For X = LBound(myArray, 1) To UBound(myArray, 1)
       Debug.Print (sht.Name)
       Debug.Print (myArray(X, fndList))
       Debug.Print (myArray(X, rplcList))
        sht.Range("A2:XFD2").Replace What:=myArray(X, fndList), Replacement:=myArray(X, rplcList), _
            LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
        Next X
        End If
        End If
        End If
        End If
      Next sht

End Sub

Edit: This is what basically happens on the sheet with the find and replace values: Before the execution in the first column are the lookup values, which are then overwritten.

I solved the issue by using an extra excel file to store the array and then load the data into vba and close it again before replacing the values in the original workbook.

Did you change the name of sheet "tab_replace"? Maybe there is a blank at the end of the worksheet name? To avoid such errors you could check the code name of the sheets. And you could propably use a name-logic that helps you to have a simpler check e.g. `IF sht.Codename like "data_*" then` — Ike, Nov 09 '21 at 09:07
You are saying nothing changed in the code. Therefor if I were you, I would shorten my lookup list. Split my screen into 2, to see VBA and excel window at the same time. Then run the macro line by line (with F8 - step over) and try to catch when this happens. — Ozgun Senyuva, Nov 09 '21 at 09:07
What do you mean by "replaces the lookup values in the left column of the table array"? Are they replaced even if they couldn't be found in the first column of `myArray`? — FaneDuru, Nov 09 '21 at 09:32
The values in the findlst column get overwritten during the execution of the `.Replace`, still don't know how this happens, but I found a workaround. — hannes101, Nov 09 '21 at 10:50
Are the tab_replace table cell values plain text or a formula ? — CDP1802, Nov 09 '21 at 16:06
I can't replicate the problem with the code shown, is there any other code run at the same time ? — CDP1802, Nov 10 '21 at 09:14
No, there was no other code running, it's just this sub, which is executed by a button or the debugging function in the vba editor. — hannes101, Nov 10 '21 at 09:58

score 1 · Accepted Answer · answered Nov 10 '21 at 11:18

I can't replicate the problem but you could try protecting the sheet before the replacements and unprotecting afterwards. This is the code I used.

Option Explicit

Sub Datastream_Code_Replacement()

    Const WS_NAME = "tab_replace"
    Const TBL_NAME = "tab_replace"
    
    Dim sht As Worksheet, tbl As ListObject
    Dim myArray As Variant
    Dim fndList As Integer, rplcList As Integer, X As Long

    Set tbl = Worksheets(WS_NAME).ListObjects(TBL_NAME)
    myArray = tbl.DataBodyRange
    
    fndList = 1
    rplcList = 2

    ' Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
    ' Skip the request table, so that no Reuters Codes are replaced
    Worksheets(WS_NAME).Protect
    For Each sht In ActiveWorkbook.Worksheets
    
        Select Case sht.Name
            Case WS_NAME, "CodeReplacement", "REQUEST_TABLE", "Hilfsfunktionen"
                ' do nothing
                Debug.Print "Skipped '" & sht.Name & "'"
            
            Case Else
                Debug.Print "Updating '" & sht.Name &  "'"
                For X = LBound(myArray) To UBound(myArray)
                    
                    sht.Range("A2:XFD2").Replace What:=myArray(X, fndList), _
                        Replacement:=myArray(X, rplcList), _
                        LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
                        SearchFormat:=False, ReplaceFormat:=False
                    Debug.Print X, myArray(X, fndList), myArray(X, rplcList)
                Next
                
         End Select
         
    Next sht
    Worksheets(WS_NAME).Unprotect
    
End Sub

find and replace values in excel using vba - lookup table overwritten

1 Answers1