Selecting cell by adjacent column name

Question

I am very new to VBA and am trying to create a macro that selects the cell next to a specifically named column, names it "UniqueID", has it apply a concatenate formula to the whole column, and then selects the next column over, names it "VerifyID", and has it apply a VLOOKUP to the whole column. What I'm having issues with is having the specific cell selection work. Here is what I have:

Application.CutCopyMode = False
Sheets("PowerBI Data Dump").Select
Selection.AutoFilter
Dim i As Long

Dim LastSamplePrepColumn As Range

Dim rngHeaders As Range

Set rngHeaders = Range("1:1")

Set LastSamplePrepColumn = rngHeaders.Find("UniqueID")
i = LastSamplePrepColumn.Column
j = LastSamplePrepColumn.Column + 1

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)"

When debugging, it errors at the last line. My main issue, however, is with having the Range().Select choosing columns by letter instead of by the name of the column. I get data dumps in a variety of formats, so I need it to be able to select a column by the name of the one next to it.

Thanks in advance for any advice.

Edit: I have changed my code to reflect where I am now. I'm still stuck on how to make the formula apply to the whole column without selecting the column by letter.

Side note: you want to [avoid using Select in your code](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — cybernetic.nomad, Feb 28 '22 at 20:37
I have made some edits, I realized that I had copied code from a different macro that wasn't necessary. I am now trying to figure out how to have the CONCATENATE and VLOOKUP formulas apply to their entire columns — Gabriella C., Mar 01 '22 at 20:25

norie · Accepted Answer · 2022-03-04T21:09:02.953

2

So you want to find the 'UniqueID' column and then add formulas in the two columns to the right and copy then down?

This code will do that but I think you might need to rethink the VLOOKUP formula.

For a start you can probably replace it with MATCH and if the column UniqueID is going to be in you might want to consider changing the relative column reference, i.e. -26, to an absolute reference.


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 = ws.Range("1:1")

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

    If Not IsError(colID) Then
        With wsData
            .Range(.Cells(2, colID + 1), .Cells(.Rows.Count, colID).End(xlUp).Offset(, 1)) _
                .FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
            .Range(.Cells(2, colID + 2), .Cells(.Rows.Count, colID).End(xlUp).Offset(, 2)) _
                .FormulaR1C1 = "=VLOOKUP(RC[-1],UniqueID!C[-26],1,FALSE)"
        End With
    End If

edited Mar 04 '22 at 21:09

answered Mar 01 '22 at 20:50

norie

9,609
2
11
18

I'm not sure how to fix it, I'm getting a runtime error 1004 "Method 'Range' of object '_Worksheet' failed" with the +1 in ".Range(.Cells(2, colID) + 1", it runs without the +1 (albeit incorrectly) so I'm not sure how to debug. – Gabriella C. Mar 02 '22 at 16:32
Only saw your comment a minute ago, that's a typo - I've fixed it, I hope. – norie Mar 04 '22 at 21:08
That runs exactly like I requested, thank you! If I might take another moment of your time, how can I redo the VLOOKUP to have an absolute reference? I always want it to look at column A of the UniqueID sheet, but I can't seem to format it in a way that VBA understands it. – Gabriella C. Mar 04 '22 at 23:40
I tried replacing UniqueID!C[-26] with UniqueID!$A:$A as other forums have suggested but it didn't work. – Gabriella C. Mar 04 '22 at 23:50
1

Nevermind, just got it working! All it took was UniqueID!C1 – Gabriella C. Mar 05 '22 at 00:10
For an absolute reference in an R1C1 formula, you need to remove the [] and specify the column number. In this case that should look like something like this - UniqueID!C1. – norie Mar 05 '22 at 00:16

Selecting cell by adjacent column name

1 Answers1