VBA Code using Table References instead of column numbers

[kkbakic]

I have a few macros that use column numbers in the code, however, whenever, I add or move columns I need to revise the code. I have other code that I've successfully used table references, but I'm struggling with this one. Understandably, I keep getting a Type Mismatch error when using the Table Reference on the 'Cells(Application.ActiveCell.Row, FL_UT).Select' rows, but I'm not sure what property/variable combination would work in this case. Here's my attempt using Table References:

'''Sub QC_DesignIssued2()

Dim strText As String
Dim MyDate As Date
Dim cl As Range

strText = Clipboard 'runs Function Clipboard to define copied text as 'Clipboard'
MyDate = Format(Now, "mm/dd/yy") 'defines MyDate as Today

'SET TABLE REFERENCE AREA - Table Name = "Table_COMPOSITE"
Set rData = ActiveWorkbook.Worksheets("Tracker").ListObjects(""Table_COMPOSITE"")

'declare object variable to column heading
Dim FL_UT As Range 'FL UT (Column heading /column #34
Dim FL_Notes As Range 'FL Design Notes (FET)/ #46
Dim FL_Permit As Range 'FL Permit Required (FET) / #49
Dim FL_PermitECD As Range 'FL Permit Received ECD (FET) / #51
Dim FL_PermitACD As Range 'FL Permit Received ACD (FET) / #52
Dim FL_IssuedACD As Range 'FL Design Issued ACD (FET) / #56

Set FL_UT = rData.ListColumns("FL UT").Range
Set FL_Notes = rData.ListColumns("FL Design Notes (FET)").Range
Set FL_Permit = rData.ListColumns("FL Permit Required (FET)").Range
Set FL_PermitECD = rData.ListColumns("FL Permit Received ECD (FET)").Range
Set FL_PermitACD = rData.ListColumns("FL Permit Received ACD (FET)").Range
Set FL_IssuedACD = rData.ListColumns("FL Design Issued ACD (FET)").Range

Dim FD_UT As Range 'FD UT (Column heading /column #64
Dim FD_Notes As Range 'FD Design Notes (FET)/ #104
Dim FD_Permit As Range 'FD Permit Required (FET) / #109
Dim FD_PermitECD As Range 'FD Permit Received ECD (FET) / #111
Dim FD_PermitACD As Range 'FD Permit Received ACD (FET) / #112
Dim FD_IssuedACD As Range 'FD Design Issued ACD (FET) / #114


Set FD_UT = rData.ListColumns("FD UT (FET)").Range
Set FD_Notes = rData.ListColumns("FD Design Notes (FET)").Range
Set FD_Permit = rData.ListColumns("FD Permit Required (FET)").Range
Set FD_PermitECD = rData.ListColumns("FD Permit Received ECD (FET)").Range
Set FD_PermitACD = rData.ListColumns("FD Permit Received ACD (FET)").Range
Set FD_IssuedACD = rData.ListColumns("FD Design Issued ACD (FET)").Range

With Worksheets("Tracker").Cells
    Set cl = .Find(strText, After:=.Range("A2"), LookIn:=xlValues)
    If Not cl Is Nothing Then
        cl.Select
        Cells(Application.ActiveCell.Row, FL_UT).Select 'FL UT (column heading)
      Else
        MsgBox strText & " Not Found"
          End If
End With

'If Project Number ("FL UT") matches StrText then populate other FL related fields
If ActiveCell = strText Then

    Cells(Application.ActiveCell.Row, FL_Notes).Select 'FL Design Notes (FET)

    ActiveCell.Value = MyDate & " Issued" & Chr(10) & ActiveCell.Value
    Cells(Application.ActiveCell.Row, FL_IssuedACD).Value = MyDate 'FL Design Issued ACD (FET)
     
    'Ask if permit is needed for FL Project and populate with UserInput
        Dim strInput_FL As String
        Dim IsPermit_FL As String
       
        IsPermit_FL = MsgBox("Is a permit required for FL UT?", vbYesNo)
               
        If IsPermit_FL = vbNo Then
            Cells(Application.ActiveCell.Row, FL_Permit).Value = "No" 'FL Permit Required (FET)
            Cells(Application.ActiveCell.Row, FL_PermitACD).Value = "01/01/01" 'FL Permit Received ACD (FET)
        Else 'IsPermit = Yes
                Cells(Application.ActiveCell.Row, FL_Permit).Value = "Yes"
                
                strInput_FL = InputBox("What is date of permit ECD for FL UT?", "Permit ECD")
                   Cells(Application.ActiveCell.Row, FL_PermitECD).Value = strInput_FL
        End If
        
 Cells(Application.ActiveCell.Row, FL_UT).Select 'Reset column numbering by selecting FL UT column again

Else  'If Project Number (StrText) doesn't match a number in the FL UT column, populate FD UT columns
 
    Cells(Application.ActiveCell.Row, FD_Notes).Select 'FD Design Notes (FET)
    ActiveCell.Value = MyDate & " Issued" & Chr(10) & ActiveCell.Value
    Cells(Application.ActiveCell.Row, FD_IssuedACD).Value = MyDate 'Design Issued ACD (FET)
    
    'Ask if permit is needed for FD Project and populate with UserInput
        Dim strInput_FD As String
        Dim IsPermit_FD As String
        'Dim PermitECD As String
        IsPermit_FD = MsgBox("Is a permit required for FD UT?", vbYesNo)
              
        If IsPermit_FD = vbNo Then
            Cells(Application.ActiveCell.Row, FD_Permit).Value = "No" 'FD Permit Required (FET)
            Cells(Application.ActiveCell.Row, FD_PermitACD).Value = "01/01/01" 'FD Permit Received ACD (FET)
        Else 'IsPermit_FD = Yes
                Cells(Application.ActiveCell.Row, FD_Permit).Value = "Yes" 'FD Permit Required (FET)
                
                strInput_FD = InputBox("What is date of permit ECD for FL UT?", "Permit ECD")
                   Cells(Application.ActiveCell.Row, FD_PermitECD).Value = strInput_FD  'FD Permit Received ECD (FET)
        End If

End If

End Sub

Function Clipboard(Optional StoreText As String) As String
'PURPOSE: Read/Write to Clipboard
'Source: ExcelHero.com (Daniel Ferry)

Dim x As Variant

'Store as variant for 64-bit VBA support
  x = StoreText

'Create HTMLFile Object
  With CreateObject("htmlfile")
    With .parentWindow.clipboardData
      Select Case True
        Case Len(StoreText)
          'Write to the clipboard
            .setData "text", x
        Case Else
          'Read from the clipboard (no variable passed through)
            Clipboard = .GetData("text")
      End Select
    End With
  End With

End Function '''

 

[CraigTriesHisBest]

This may be a bodge but here goes. I had something similar where I wanted to get values from a column with a specific column header on row 1.

What I did was find the column by searching for the column header value and using a bit of VBA I saw online to convert the column number to a letter.

In VBA I said
s_ColumnSourceLetter = NumberToLetter(WorksheetFunction.Match(s_ColumnSource, Range("1:1"), 0))

where s_ColumnSourceLetter is the letter with the matching column header
and s_ColumnSource is the string I want to find in row 1 to identify my column
and NumberToLetter is the following VBA I found somewhere

Function NumberToLetter(input_Col As Long) As String
Dim a As Long, b As Long
a = input_Col
NumberToLetter = ""
Do While input_Col > 0
a = Int((input_Col - 1) / 26)
b = (input_Col - 1) Mod 26
NumberToLetter = Chr(b + 65) & NumberToLetter
input_Col = a
Loop
End Function

If you just want the column number you could ignore the VBA above and just use
i_column_number = WorksheetFunction.Match("string_to_find_in_row_1_to_identify_your_column", Range("1:1"), 0))

Hope this helps (but be gentle, it is my first reply)

Craig