VBA Application.Match keeps throwing 2042 error

logandiana

Board Regular
Joined
Feb 21, 2017
Messages
94
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.
VBA Code:
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.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

juddaaaa

Board Regular
Joined
Jan 4, 2020
Messages
208
Office Version
  1. 365
Platform
  1. Windows
This little function will return the column number based on the string you pass in
VBA Code:
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
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,069
Messages
5,545,803
Members
410,708
Latest member
SanTrapGamer
Top