Adding VLookup in VBA using variable sheet name

[jbennett01]

I need to dynamically add sheets to my workbook and then add VLookup formulas to my main sheet that reference the newly added sheets. I did this easily with a static name but doing it dynamically has eluded me. I have reviewed an answer to this question on this forum and could not get that to work. I found a solution on the MicroSoft forum and that functions.

What I started with:

Dim strNewSheetName As String
strNewSheetName = "Upload 2023-1-2"
ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-2],strNewSheetName!C[10],1)"

This tried to open a new file. From the MS forum I tried changing the formula line to this.

ActiveCell.FormulaR1C1 = "=+VLOOKUP(C[-2],'" & strNewSheetName & "'!C[10],1)"

That seems to work but I am concerned how it will function as I modify the code to add several more sheets to the workbook. I do not really understand the 'R1C1' or any of those cell references, such as C[-2] or C[10].

Is this the best way or is there a better approach to this?

Thank you.

 

[6StringJazzer]

First you don't need the "+".

The R1C1 format gives relative references to whatever cell the formula is in, rather than specific letters and numbers. This is an advantage in VBA when you want to assign the same formula in many rows or columns in one line of code.

R means Row and C means Column. If the number has brackets around it, it means that is relative to the cell containing the formula.
R[1]C[1] means 1 row down and 1 column to the right
In A1: B2
In D14: E15
R1C1 means row 1, column 1. It is absolute and always means A1.

In your case, your cell will always get the right formula, without having to determine the corresponding letter for the column dynamically.

Your code looks fine to me. If it works, keep it.

 

[jbennett01]

First you don't need the "+".

The R1C1 format gives relative references to whatever cell the formula is in, rather than specific letters and numbers. This is an advantage in VBA when you want to assign the same formula in many rows or columns in one line of code.

R means Row and C means Column. If the number has brackets around it, it means that is relative to the cell containing the formula.
R[1]C[1] means 1 row down and 1 column to the right
In A1: B2
In D14: E15
R1C1 means row 1, column 1. It is absolute and always means A1.

In your case, your cell will always get the right formula, without having to determine the corresponding letter for the column dynamically.

Your code looks fine to me. If it works, keep it.

Thank you.

 

[jbennett01]

The macro executed and the formula populated fine all the way down the column as macro added new sheets to the workbook. However, at some point the formula stopped working correctly. Looking back, I see I forgot to mention I'm working with a filtered list where the formula is placed. Does that change things? Do I need to use something with SpecialCells and xlCellTypeVisible?

Here is the actual code of the loop I am using to move down the columns and add the formula:

 Do While i > 0
        ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-2],'" & strNewSheetName & "'!C[10],1)"
        ActiveCell.Offset(1, 0).Activate
        Do Until Selection.EntireRow.Hidden = False
            If Selection.EntireRow.Hidden = True Then
                ActiveCell.Offset(1, 0).Activate
            End If
        Loop
    i = i - 1
Loop

Also, to confirm I understand the Cell References in this formula ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-2],'" & strNewSheetName & "'!C[10],1)"
R1=Row 1; C1 = Column 1, C[-2] is a relative reference and tells VBA to look at the column 2 places to the left; C[10] is also relative and tells VBA to look at the column 10 places to the right of the same column in the data sheet, so if the formula is on going in column J, the formula will match the data from column H in the main sheet to column T in the data sheet. Is that correct?