VBA Application.Match keeps throwing 2042 error

[logandiana]

I have to fill columns of a template using column heading names, because sometimes the template shifts and the columns move.
I have to find which column is correct before adding the data under the header.
I am using the following code, and I keep getting ERROR 2042, but I am only getting the error on some of the lines. Some lines pull back the column number as expected.

Dim TMPL, NewFile As Workbook
Dim wksht, temp As Worksheet
Dim LR, LC As Long
Dim CURCOL, MCH1, MCH2, MCH3, MCH4, MCH5, MCH6, MCH7, MCH8, MCH9, MCH10, MCH11, MCH12, MCH13, MCH14, MCH15, MCH16, MCH17 As Variant
Dim who, LCOL, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17 As String
Sub DoStuff()
Application.ScreenUpdating = False
Set TMPL = ThisWorkbook
Set wksht = TMPL.Sheets("WORKSHEET")
Set temp = TMPL.Sheets("Template")
who = Environ("USERNAME")
LR = wksht.Cells(Rows.Count, 1).End(xlUp).Row
With temp
LC = temp.Cells(1, Columns.Count).End(xlToLeft).Column
LCOL = Split(Cells(1, LC).Address, "$")(1)
CURCOL = "Logical Partner"
MCH1 = Application.Match(CURCOL, temp.Range("A1:" & LCOL & "1"), 0)
CURCOL = "Order type"
MCH2 = Application.Match(CURCOL, temp.Range("A1:" & LCOL & "1"), 0)
CURCOL = "Sales org"
MCH3 = Application.Match(CURCOL, temp.Range("A1:" & LCOL & "1"), 0)
CURCOL = "Dist chnl"
MCH4 = Application.Match(CURCOL, temp.Range("A1:" & LCOL & "1"), 0)
CURCOL = "Division"
MCH5 = Application.Match(CURCOL, temp.Range("A1:" & LCOL & "1"), 0)
CURCOL = "Order Taken By"
MCH6 = Application.Match(CURCOL, temp.Range("A1:" & LCOL & "1"), 0)
CURCOL = "PO no."
MCH7 = Application.Match(CURCOL, temp.Range("A1:" & LCOL & "1"), 0)
CURCOL = "PO date"
MCH8 = Application.Match(CURCOL, temp.Range("A1:" & LCOL & "1"), 0)
CURCOL = "Sold-to-Customer"
MCH9 = Application.Match(CURCOL, temp.Range("A1:" & LCOL & "1"), 0)
CURCOL = "Ord reason"
MCH10 = Application.Match(CURCOL, temp.Range("A1:" & LCOL & "1"), 0)
CURCOL = "PO type"
MCH11 = Application.Match(CURCOL, temp.Range("A1:" & LCOL & "1"), 0)
CURCOL = "Item Qty"
MCH12 = Application.Match(CURCOL, temp.Range("A1:" & LCOL & "1"), 0)
CURCOL = "Serial Number"
MCH13 = Application.Match(CURCOL, temp.Range("A1:" & LCOL & "1"), 0)
CURCOL = "Cust ref"
MCH14 = Application.Match(CURCOL, temp.Range("A1:" & LCOL & "1"), 0)
CURCOL = "Header Text ID 2"
MCH15 = Application.Match(CURCOL, temp.Range("A1:" & LCOL & "1"), 0)
CURCOL = "Header Text"
MCH16 = Application.Match(CURCOL, temp.Range("A1:" & LCOL & "1"), 0)
CURCOL = "Sequence"
MCH17 = Application.Match(CURCOL, temp.Range("A1:" & LCOL & "1"), 0)
...

What is going on here?
MCH1 > error
MCH2 > error
MCH3 > error
MCH4 > error
MCH5 > just fine, returns column 6 as expected
MCH6 > error
MCH7 > error
and so on.
I have checked and checked and checked and CHECKED and the data I am trying to match is there and correct! I've even done a TRIM and CLEAN on the data being looked up.

 

[juddaaaa]

This little function will return the column number based on the string you pass in

Function GetColumnNumber(header As String, RNG As Range) As Long

    Dim Found As Range

    Set Found = RNG.Find( _
        What:=header, _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByColumns)
       
    If Not Found Is Nothing Then
        GetColumnNumber = Found.Column
    End If

End Function

You can then just call it like this

Dim wksht, temp As Worksheet
Dim LR As Long
Dim MCH1, MCH2, MCH3, MCH4, MCH5, MCH6, MCH7, MCH8, MCH9, MCH10, MCH11, MCH12, MCH13, MCH14, MCH15, MCH16, MCH17 As Variant
Dim who, LCOL, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17 As String

Sub DoStuff()

    Dim RNG As Range
    
    Application.ScreenUpdating = False
    
    Set TMPL = ThisWorkbook
    Set wksht = TMPL.Sheets("WORKSHEET")
    Set temp = TMPL.Sheets("Template")
    who = Environ("USERNAME")
    LR = wksht.Cells(Rows.Count, 1).End(xlUp).Row
    
    With temp
        Set RNG = .Range(.Cells(1, 1), .Cells(1, 1).End(xlToLeft))
        
        MCH1 = GetColumnNumber("Logical Partner", RNG)
        MCH2 = GetColumnNumber("Order type", RNG)
        MCH3 = GetColumnNumber("Sales org", RNG)
        MCH4 = GetColumnNumber("Dist chnl", RNG)
        MCH5 = GetColumnNumber("Division", RNG)
        MCH6 = GetColumnNumber("Order Taken By", RNG)
        MCH7 = GetColumnNumber("PO no.", RNG)
        MCH8 = GetColumnNumber("PO date", RNG)
        MCH9 = GetColumnNumber("Sold-to-Customer", RNG)
        MCH10 = GetColumnNumber("Ord reason", RNG)
        MCH11 = GetColumnNumber("PO type", RNG)
        MCH12 = GetColumnNumber("Item Qty", RNG)
        MCH13 = GetColumnNumber("Serial Number", RNG)
        MCH14 = GetColumnNumber("Cust ref", RNG)
        '// ....
    End With
    
    Application.ScreenUpdating = True

End Sub

Give it a go and let me know how you get on