VBA Application.Match keeps throwing 2042 error

logandiana

Board Regular
Joined
Feb 21, 2017
Messages
107
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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
 
Upvote 0

Forum statistics

Threads
1,223,943
Messages
6,175,551
Members
452,652
Latest member
eduedu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top