I am using basically the solution from this question 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.

CodePudding user response：

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