CODEWITHSUNDEEP
excelvbavlookup
Serveira
Serveira

Reputation: 51

VLOOKUP from another sheet, apply formula every nth row

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.

Upvotes: 0

Views: 422

Answers (1)

Shai Rado
Shai Rado

Reputation: 33692

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

Upvotes: 0

Related Questions