Find/Replace based on table NOT WORKING - in SOME cases? Not sure why....?

[jwb1012]

Hello, the code below seems to break at the occurrence of the special character "_" on the headings with "TXN"?? Or that have "_" and "two_words" vs. a "word_number". Any thoughts why it cant handle that / how I can fix it?



ORIGINAL DATA SET

DMC_1	NSN_1	Date	Quantity	Unit Price	Total Price
AA	000001	15-Mar-2017	20	8.01	$ 160.20
BB	000002	15-Mar-2017	20	40.04	$ 800.80

<tbody>
</tbody><colgroup><col span="6"></colgroup>

TABLE AFTER MACRO (BELOW IS RUN):

DMC	NSN	TXN Date	TXN Quantity	TXN Unit Price	TXN Total Price
AA	000001	15-Mar-2017	20	8.01	$ 160.20
BB	000002	15-Mar-2017	20	40.04	$ 800.80

<tbody>
</tbody><colgroup><col span="6"></colgroup>

LOOKUP TABLE

Import Headings	Report Headings
DMC	DMC_1
NSN	NSN_1
TXN_Date	Date
TXN_Quantity	Quantity
TXN_Unit_Price	Unit Price
TXN_Total_Price	Total Price

<tbody>
</tbody><colgroup><col span="8"></colgroup>

CODE TO REPLACE COLUM HEADINGS BASED ON LOOKUP TABLE:

Sub Replace_Column_Headings()
    
    Dim rngData     As Range
    Dim rngLookup   As Range
    Dim Lookup      As Range
    
    With Sheets("2 - Report")
        Set rngData = .Range("$A$1", "$H$1")
    End With
    
    With Sheets("1 - Workbook Details")
        Set rngLookup = .Range("$K$25", "$K$30")
    End With
    
    For Each Lookup In rngLookup
        If Lookup.Value <> "" Then
            rngData.Replace What:=Lookup.Value, _
                            Replacement:=Lookup.Offset(0, -4).Value, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByColumns, _
                            MatchCase:=False
        End If
    Next Lookup
    
End Sub

 

[myall_blues]

It works quite happily for me (Excel 2013, Windows). What error do you get?

Regards

Murray