Subtracting from a column based on column names in

Question

I have a column named "Re-Align Midpoint" whose result is the date of another column (named "Final Execution Date") minus 100 days. Right now, the "Re-Align Midpoint" date is in column AZ while "Final Execution Date" is in column BR. These columns are subject to be moved around, so it is impractical for me to build a macro based on column indexes. What I would like to do instead is subtract one from the other based on column names. The header rows are on row 2, not row 1

This is what I have for my recorded macro:

Sub Re_Align_Midpoint_Date

    Range("AZ3").Select
    ActiveCell.FormulaR1C1 = "=RC[18] - 100"
    Range("AZ3").Select
    Selection.AutoFill Destination:=Range("AZ3:AZ142"), Type:=xlFillDefault
    Range("AZ3:AZ142").Select
    ActiveWindow.SmallScroll Down:=-132
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Answer 1

As long as the column headings won't change you can use an hLookup to do this:

=HLOOKUP("Final Execution Date",$A$2:$ZZ3,ROW(A2),FALSE)-100

Just paste this formula at the top of your "Midpoint" column and fill down.