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 would work. 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
Set rData = ActiveWorkbook.Worksheets("Tracker").ListObjects("Table_COMPOSITE")
'declare object variable to column heading
Dim FL_UT As Range 'FL UT (Column heading)
Dim FL_Notes As Range 'FL Design Notes (FET)
Dim FL_Permit As Range 'FL Permit Required (FET)
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
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
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
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 FL_UT number 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?")
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)
End If
End If
End Sub
Here's one way to approach this without using ActiveCell:
Sub Tester()
Dim ws As Worksheet, rData As ListObject, FL_UT As Long, FL_Notes As Long, FL_Permit As Long
Dim f As Range, rw As Range
Set ws = ActiveWorkbook.Worksheets("Tracker")
Set rData = ws.ListObjects("Table_COMPOSITE")
'grab the list column indexes
FL_UT = rData.ListColumns("FL UT").Index 'column # in table
FL_Notes = rData.ListColumns("FL Design Notes (FET)").Index
FL_Permit = rData.ListColumns("FL Permit Required (FET)").Index
'does this not need to search a specific range/column?
Set f = ws.Cells.Find("blahblah", After:=ws.Range("A2"), _
LookIn:=xlValues, lookat:=xlWhole) 'or xlPart
If Not f Is Nothing Then
'get the listobject row for the found cell
Set rw = Application.Intersect(f.EntireRow, rData.DataBodyRange)
'use the column indexes to update the row
rw.Cells(FL_UT) = "A"
rw.Cells(FL_Notes) = "B"
rw.Cells(FL_Permit) = "C"
Else
MsgBox "Not found"
End If
End Sub