Issues with variable headings

[melliot2]

Okay here is a fun one I am using the below VBA to try to give headings the names pulled in from our data system. The headings may change so I have all of the base part of the heading defined as variables. Now I want to replace the variable with the heading that is pulled in if it is pulled in otherwise put the base heading in. It keeps erroring on me any clue as to what I am doing wrong.

Dim MyCols
Dim a As Long, FC As Long
Dim AP As String
Dim approval As String
Dim dex As String
Dim dp1 As String
Dim dp2 As String
Dim ds As String
Dim fx As String
Dim hc As String
Dim hs As String
Dim i2 As String
Dim iva As String
Dim malt As String
Dim ph As String
Dim temp As String
Dim tri As String
Dim bac As String
Dim debris As String
Dim mold As String
Dim yeast As String
Dim acetalde As String
Dim comments As String
Dim col As Integer
AP = "2AP"
approval = "APPROVAL"
dex = "DEX"
dp1 = "DP1"
dp2 = "DP2"
ds = "DS"
fx = "FX"
hc = "HC"
i2 = "I2"
iva = "IVA"
malt = "MALT"
ph = "PH"
temp = "TEMP"
tri = "TRI"
bac = "BACTERIA"
debris = "DEBRIS"
mold = "MOLD"
yeast = "YEAST"
acetalde = "ACETALDE"
comments = "COMMENTS"
'If InStr(1, ActiveSheet.Range("R1C" & col).Value, "CHECKED OUT") > 0 Then
col = 7
Do
col = col + 1
If ActiveSheet.Range("R1C" & col).Text Like AP Then AP = ActiveSheet.Range("R1C" & col).Text Else
If ActiveSheet.Range("R1C" & col).Text Like approval Then approval = ActiveSheet.Range("R1C" & col).Text Else
If ActiveSheet.Range("R1C" & col).Text Like dex Then dex = ActiveSheet.Range("R1C" & col).Text Else
If ActiveSheet.Range("R1C" & col).Text Like dp1 Then dp1 = ActiveSheet.Range("R1C" & col).Text Else
If ActiveSheet.Range("R1C" & col).Text Like dp2 Then dp2 = ActiveSheet.Range("R1C" & col).Text Else
If ActiveSheet.Range("R1C" & col).Text Like ds Then ds = ActiveSheet.Range("R1C" & col).Text Else
If ActiveSheet.Range("R1C" & col).Text Like hc Then hc = ActiveSheet.Range("R1C" & col).Text Else
If ActiveSheet.Range("R1C" & col).Text Like hs Then hs = ActiveSheet.Range("R1C" & col).Text Else
If ActiveSheet.Range("R1C" & col).Text Like i2 Then i2 = ActiveSheet.Range("R1C" & col).Text Else
If ActiveSheet.Range("R1C" & col).Text Like iva Then iva = ActiveSheet.Range("R1C" & col).Text Else
If ActiveSheet.Range("R1C" & col).Text Like malt Then malt = ActiveSheet.Range("R1C" & col).Text Else
If ActiveSheet.Range("R1C" & col).Text Like ph Then ph = ActiveSheet.Range("R1C" & col).Text Else
If ActiveSheet.Range("R1C" & col).Text Like temp Then temp = ActiveSheet.Range("R1C" & col).Text Else
If ActiveSheet.Range("R1C" & col).Text Like tri Then tri = ActiveSheet.Range("R1C" & col).Text Else
If ActiveSheet.Range("R1C" & col).Text Like bac Then bac = ActiveSheet.Range("R1C" & col).Text Else
If ActiveSheet.Range("R1C" & col).Text Like debris Then debris = ActiveSheet.Range("R1C" & col).Text Else
If ActiveSheet.Range("R1C" & col).Text Like mold Then mold = ActiveSheet.Range("R1C" & col).Text Else
If ActiveSheet.Range("R1C" & col).Text Like yeast Then yeast = ActiveSheet.Range("R1C" & col).Text Else
If ActiveSheet.Range("R1C" & col).Text Like acetalde Then acetalde = ActiveSheet.Range("R1C" & col).Text Else
If ActiveSheet.Range("R1C" & col).Text Like comments Then comments = ActiveSheet.Range("R1C" & col).Text Else
col = col + 1
Loop Until col = 29

Application.ScreenUpdating = False
MyCols = Array("Process_datetime", "Carrier Info", "Customer", "Product", "Load Order#", "Weight", "Lot Number", _
AP, approval, dex, dp1, dp2, ds, fx, hc, hs, i2, iva, malt, ph, temp, tri, bac, debris, mold, yeast, _
acetalde, comments)

For a = LBound(MyCols) To UBound(MyCols)
FC = 0
On Error Resume Next
FC = Application.Match(MyCols(a), Rows(1), 0)
On Error GoTo 0
If FC = 0 Then
Columns(a).Insert
Cells(1, a).Value = MyCols(a)
ElseIf a = FC Then
'do nothing
Else
Columns(a).Insert
Columns(FC + 1).Cut Destination:=Columns(a)
End If
Next a
ActiveSheet.UsedRange.Columns.AutoFit
Application.ScreenUpdating = True

 

[melliot2]

P.S. I tried this line and it didn't work either
'If InStr(1, ActiveSheet.Range("R1C" & col).Value, "CHECKED OUT") > 0 Then