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

[TABLE="width: 696"]
<tbody>[TR]
[TD]DMC_1[/TD]
[TD]NSN_1[/TD]
[TD]Date[/TD]
[TD]Quantity[/TD]
[TD]Unit Price[/TD]
[TD]Total Price[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]000001[/TD]
[TD]15-Mar-2017[/TD]
[TD]20[/TD]
[TD]8.01[/TD]
[TD] $ 160.20 [/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]000002[/TD]
[TD]15-Mar-2017[/TD]
[TD]20[/TD]
[TD]40.04[/TD]
[TD] $ 800.80 [/TD]
[/TR]
</tbody><colgroup><col span="6"></colgroup>[/TABLE]


TABLE AFTER MACRO (BELOW IS RUN):
[TABLE="width: 696"]
<tbody>[TR]
[TD]DMC[/TD]
[TD]NSN[/TD]
[TD]TXN Date[/TD]
[TD]TXN Quantity[/TD]
[TD]TXN Unit Price[/TD]
[TD]TXN Total Price[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]000001[/TD]
[TD]15-Mar-2017[/TD]
[TD]20[/TD]
[TD]8.01[/TD]
[TD] $ 160.20 [/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]000002[/TD]
[TD]15-Mar-2017[/TD]
[TD]20[/TD]
[TD]40.04[/TD]
[TD] $ 800.80 [/TD]
[/TR]
</tbody><colgroup><col span="6"></colgroup>[/TABLE]


LOOKUP TABLE
[TABLE="width: 544"]
<tbody>[TR]
[TD="colspan: 4"]Import Headings[/TD]
[TD="colspan: 4"]Report Headings[/TD]
[/TR]
[TR]
[TD="colspan: 4"]DMC[/TD]
[TD]DMC_1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 4"]NSN[/TD]
[TD]NSN_1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 4"]TXN_Date[/TD]
[TD]Date[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 4"]TXN_Quantity[/TD]
[TD]Quantity[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 4"]TXN_Unit_Price[/TD]
[TD="colspan: 2"]Unit Price[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 4"]TXN_Total_Price[/TD]
[TD="colspan: 2"]Total Price[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col span="8"></colgroup>[/TABLE]


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