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:
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:
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!
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!