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
<tbody>
</tbody><colgroup><col span="6"></colgroup>
TABLE AFTER MACRO (BELOW IS RUN):
<tbody>
</tbody><colgroup><col span="6"></colgroup>
LOOKUP TABLE
<tbody>
</tbody><colgroup><col span="8"></colgroup>
CODE TO REPLACE COLUM HEADINGS BASED ON LOOKUP TABLE:
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:
Code:
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