• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to change reference in the vLookup used in the loop

ThrottleWorks

ThrottleWorks

Excel Ninja
Sir, I am using a loop.

I am using a vlookup formula in the loop.


Loop type is For Each.


My range start from cell B3, so I have used range B3 as the reference in the vlookup.

My problem is for the entire loop i.e. cell B63, vlookup use cell b3 as the reference.


It is not getting changed, I want it to be cell b3,b4,b5,b6 and so on.


Can anyone please help me in modifying the vlookup formula.
 
Hi Sachin,


Try this,

[pre]
Code: 
Sub MyVlookUp()

Dim MyVal As Integer
Dim LstRow As Long

LstRow = Sheet2.Range("A" & Rows.Count).End(xlUp).Row

Sheet2.Activate

For MyVal = 2 To LstRow

On Error Resume Next

Range("B" & (MyVal)).Value = Application.WorksheetFunction.VLookup(Range("A" & (MyVal)).Value, Sheet3.Range("A:B"), 2, 0)

Next

End Sub

Assumption:


Data in sheet2 are as follows (From A1 To B11):

[pre][code](ColA)           (Col B)

Lookup vals 	 Values
A
B
C
D
E
F
G
H
I
J
[/pre]
Data in sheet3 are as follows(From A1 to B3):

(ColA) (Col B)
D 4
J 5
A 6[/code][/pre]
We are doing vlookup in sheet2 from Sheet3.


Hope this helps.


Kaushik
 
Hi I am getting a bug while using vlookup, Run time error 1004

Unable to get the vlookup property of the worksheetfunction class


I am trying to execute following line.


activecell.value = application.worksheetfunction.vlookup(Range("b" & (myval)).value,data.range("e:cl"),86,0)


MyVal & Data are defined

Myval is integer

Data is worksheet from another workbook.


Can anyone help me in this please.
 
Hi Sachin,


Try this:


ActiveCell.Value = Application.WorksheetFunction.VLookup(Range("b" & (myval)).Value, Workbooks("workbook name").Worksheets("data").Range("e:cl"), 86, 0)


Note: Make sure the workbook, from where you are fetching the data, is open.


Kaushik
 
Hi Kaushik Sir, sorry for late reply.


I am trying this, will share the results soon.


Thanks a lot for the help, have a nice day.
 
I think I am able to solve it now.

I have used following code please correct me if I ma wrong.


Dim MyVal as integer

For Each rn in rng

If rn.value <> "" then

MyVal = rn.row

my vlookup code goes here

End If

Next


Kaushik Sir thanks a lot for the help, have a nice day.
 
You must log in or register to reply here.
Back
Top