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.
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.
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)
...
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.