How to reference a column name in a VBA formula?

[caprie13]

As of now I have an IF function in VBA that references column locations - eg. IF(R4=1,"Complete",SUM(AB4:AB1000))

There will be situations where I can't reference columns R or AB because sometimes columns get deleted. I would need to reference columns called CompletionLevel (this is column R normally, but won't always be) and Quantity (this is column AB normally, but won't always be). The column headers that I want to reference will always be in row 3.

I think I'll need to use the Find function but I'm not totally sure how to write what I need in VBA. Would I need to define these columns at the start of my code, and how would I specify that I want it to look at the cell right under the column header (aka R4 and AB4:AB1000)?

 

[Giordano Bruno]

x = Range("CompletionLevel").Column. Then use the Cells method rather than the Range.

 

[Peter_SSs]

I think I'll need to use the Find function but I'm not totally sure how to write what I need in VBA. Would I need to define these columns at the start of my code, and how would I specify that I want it to look at the cell right under the column header (aka R4 and AB4:AB1000)?

Try something like this. I've assumed that you want the formula to go in column "D" in the first row under the header row.

Sub InsertFormula()
  Dim CLcol As String, Qcol As String
  
  Const HeaderRow As Long = 3 '<-Adjust as required
  
  With Rows(HeaderRow)
    CLcol = Split(.Find(What:="CompletionLevel", LookAt:=xlWhole).Address, "$")(1)
    Qcol = Split(.Find(What:="Quantity", LookAt:=xlWhole).Address, "$")(1)
  End With
  Range("D" & HeaderRow + 1).Formula = Replace(Replace(Replace("=IF(#^=1,""Complete"",SUM(%^:%1000))", "^", HeaderRow + 1), "#", CLcol), "%", Qcol)
End Sub