Adding VLookup in VBA using variable sheet name

jbennett01

New Member
Joined
Apr 25, 2018
Messages
6
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:

VBA Code:
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.

VBA Code:
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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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.
 
Upvote 0
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.
 
Upvote 0
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:

VBA Code:
 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?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top