How to apply formula to column by header name

[gcutler]

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:

    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:

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!

 

[gcutler]

Actually got this working, posting my solution 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

Hope this helps someone!