VBA How to apply formula to column by header name

Question

Hello,

I am attempting to make a macro that applies a formula to an entire column based on the header, not the index letter. Here is what I have so far that does work:

VBA Code:
Application.CutCopyMode = False
Sheets("PowerBI Data Dump").Select
Selection.AutoFilter
Dim i As Long
Dim j As Long
Dim wsData As Worksheet
Set wsData = Sheets("PowerBI Data Dump")

Dim LastSamplePrepColumn As Range

Dim rngHeaders As Range

Set rngHeaders = Range("1:1")

Set LastSamplePrepColumn = rngHeaders.Find("UniqueID")
i = Application.Match("UniqueID", rngHeaders, 0)
j = LastSamplePrepColumn.Column + 1

It's at this point that I've tried a variety of things, none of which work. Here's an example of what I have tried that didn't work:\

VBA Code:
ActiveSheet.Cells(2, i).Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
Selection.AutoFill Destination:=Range("RC2:RC157")
ActiveSheet.Cells(1, j).Select
ActiveCell.FormulaR1C1 = "VerifyID"
ActiveSheet.Cells(2, j).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],UniqueID!C[-26],1,FALSE)"

I want the UniqueID column to have a concatenate formula as follows: "=CONCATENATE(RC[-2],RC[-1])"
and I want the column next to it VerifyID to have a VLOOKUP formula as follows "=VLOOKUP(RC[-1],UniqueID!C[-26],1,FALSE)". For this second one, I have been told that it might be better if I make it a MATCH instead of a VLOOKUP, but I'm not sure how that would work better. My issue with the VLOOKUP is that the column it's comparing to isn't always at index number 26, but is always the column before it, UniqueID.

I realize this is a big ask, I appreciate any advice or guidance to help me get started in the right direction. Thank you!

Answer 1

Actually got this working, posting my solution here in case anyone else runs into a similar issue:

Sub FinalFormat()
'
    Dim wsData As Worksheet
    Dim LastSamplePrepColumn As Range
    Dim rngHeaders As Range
    Dim colID As Long

    Application.CutCopyMode = False

    Set wsData = Sheets("PowerBI Data Dump")

    Set rngHeaders = wsData.Range("1:1")

    colID = Application.Match("UniqueID", rngHeaders, 0)

    If Not IsError(colID) Then
        With wsData
            .Range(.Cells(2, colID), .Cells(.Rows.Count, colID).End(xlUp)) _
                .FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
            .Range(.Cells(2, colID + 1), .Cells(.Rows.Count, colID).End(xlUp).Offset(, 1)) _
                .FormulaR1C1 = "=VLOOKUP(RC[-1],UniqueID!C1,1,FALSE)"
        End With
    End If
    Sheets("PowerBI Data Dump").Select
End Sub