Set Range = Column Title Name (ex: "Object"), rather than Column Letter/Number (ex: "C2")

I want to define a range in VBA by using the column string name, rather than the column letter/number identifier. Here is how I'm currently doing this:

Dim objectRange As range
Set objectRange = AMPTab.range("C2", AMPTab.range("C" & AMPTab.Rows.Count).End(xlUp))

From the code above, I'm basically setting everything in column C2 (until the last non-empty cell in the column) within the AMPTab sheet equal to a range called objectRange.

Instead of using "C2" and "C", is there any way I can use the column title? For example, column C has a title of "Object: Name", and would like to use that instead of the column letter:

The whole purpose is that the "Object: Name" column heading will not change, but the column letter may change from C --> D, for example.

Thank you!

Solution 1:

Sub find_col()
Dim objectRange As Range
Set amptab = ThisWorkbook.Worksheets("database")
If Not amptab.Range("1:1").Find("Object: Name", lookat:=xlWhole) Is Nothing Then
name_col = amptab.Range("1:1").Find("Object: Name", lookat:=xlWhole).Column
Set objectRange = amptab.Range(amptab.Cells(2, name_col), amptab.Cells(amptab.Rows.Count, name_col).End(xlUp))
Debug.Print objectRange.Address
End If
End Sub