I am wanting to check if part of a header exist starting in Row-1 Column-Y and going Through Column-AL. If that part of the header does not exist to create it. If it does exist then do nothing. I can create the header if it does not exist. The part I am missing is the Else if there is a successful match found. Can someone help me fix the error?
VBA Code:
Sub testheaders()
Application.ScreenUpdating = False
Dim arrCols, sht As Worksheet, s
Dim LastCol As Long, HeaderRng As Range
'All the fields in the final version in specific order needed
arrCols = Array("Sales Order #", "Order Date", "Days On Order", "Order Class", "Ship Method", "BO Ship Method", "Line Status", "Order Qty", "Allocated QtY", "Invoiced Qty", "BO Qty", "Comment", "Weight (lbs)", "Completed")
Set sht = ActiveSheet
With sht
' find last column with data in the first row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
' set the scanned range with data in the Header row
Set HeaderRng = .Range(.Cells(1, 25), .Cells(25, LastCol))
For Each s In arrCols
If IsError(Application.Match(s, HeaderRng, 0)) Then '<-- no Match
MsgBox s & " is a missing header"
Range("Y1").FormulaR1C1 = "Sales Order #"
Range("Z1").FormulaR1C1 = "Order Date"
Range("AA1").FormulaR1C1 = "Days On Order"
Range("AB1").FormulaR1C1 = "Order Class"
Range("AC1").FormulaR1C1 = "Ship Method"
Range("AD1").FormulaR1C1 = "BO Ship Method"
Range("AE1").FormulaR1C1 = "Line Status"
Range("AF1").FormulaR1C1 = "Order Qty"
Range("AG1").FormulaR1C1 = "Allocated Qty"
Range("AH1").FormulaR1C1 = "Invoiced Qty"
Range("AI1").FormulaR1C1 = "BO Qty"
Range("AJ1").FormulaR1C1 = "Comment"
Range("AK1").FormulaR1C1 = "Weight (lbs)"
Range("AL1").FormulaR1C1 = "Completed"
Else '<-- successful match
MsgBox s & " header found"
End If
Next s
End With
Application.ScreenUpdating = False
End Sub