VLOOKUP from another sheet, apply formula every nth row

Question

I'm working on the below formula to Vlookup data from another sheet. The formula must be placed on the 14th column, and every 7 rows, vlookuping the first column value.

Sub test3()
'Vlookuping on Column N 


Dim lastRow As Long
lastRow = Cells(Rows.Count, 14).End(xlUp).Row 'Checks last row with data

Dim cel As Range, rng As Range

Dim sheetName, lookupFrom, myRange           'variables
sheetName = "Plan2" 'the worksheet i want to get data from
lookupFrom = ActiveCell.Offset(0, -14).Address '
myRange = "'" & sheetName & "'!1:1048576"

For i = 3 To lastRow Step 7 '
    Cells(i, 14).Select      'i= first value; step= lines to jump
    ActiveCell.Formula = "=VLOOKUP(" & lookupFrom & ";" & myRange & "; 14; FALSE)"

Next i

End Sub

Example Sheet

I want to place the formula on the pink cells (column N), vlookuping the pink value from the first cell on another worksheet. My actual formula isn't even executing.

To make the formula work, replace the ; with ,. vba is very US English centric and the formula must be entered in as if your computer were set to US English locality. — Scott Craner
Thanks, it almost works. However it opens a box for me to select the sheet i want to extract data from for every variable. Is there a way i could design the macro to work with this sheet only? — Serveira
Then check your spelling on the Plan2. make sure it is spelled exactly as the actual sheet name. — Scott Craner
It's painful to watch, please modify your last 2 lines to : Cells(i, 14).Formula = "=VLOOKUP(" & lookupFrom & "," & myRange & ", 14, FALSE)" — Shai Rado
@Serveira is the Vlookup found a match on ColumnA in "Plan2" sheet, does it return the value of the 14th Column in "Plan2" ? or from this sheet ? (where you placed the image of the table) — Shai Rado

Shai Rado Shai Rado · Accepted Answer · 2016-09-13T14:16:58

Try the code below, with 2 exceptions:

1.Modify "VlookRes" to your Sheet name - where you want to results to be.

2.You have Merged Cells in Column A (according to your image uploaded), you are merging Rows 2 untill 6 in column A, this means that the value of Cell A3 will be 0. If you want the values to read from the third row, start the merging from row 3 (and soon for the next values in Column A).

Option Explicit

Sub test3()

'Vlookuping on Column N

Dim ShtPlan     As Worksheet
Dim ActSht      As Worksheet
Dim lastRow     As Long
Dim sheetName   As String
Dim lookupFrom  As String
Dim myRange     As String
Dim i           As Long

' modify this Sheet Name to your sheet name (where you want to keep your results)
Set ActSht = Sheets("VlookRes")
lastRow = ActSht.Cells(ActSht.Rows.Count, 14).End(xlUp).Row ' Checks last row with data

sheetName = "Plan2" 'the worksheet i want to get data from
Set ShtPlan = Sheets(sheetName)

myRange = "'" & sheetName & "'!1:1048576"

For i = 3 To lastRow Step 7
    lookupFrom = ActSht.Cells(i, 1).Address ' ActiveCell.Offset(0, -14).Address '
    Cells(i, 14).Formula = "=VLOOKUP(" & lookupFrom & "," & myRange & ", 14, FALSE)"
Next i

End Sub

VLOOKUP from another sheet, apply formula every nth row

1 Answers