Loop function with vlookup

Hey all, i'm back!

Starting my second internship with the same company. I've got a question for everyone, How can I make a loop function in VBA work with a vlookup function. This is the best i've done, but no such luck.

Sub Vlookup()

' This Macro runs as long as there is something in the previous column
Do While IsEmpty(ActiveCell.Offset(0, -6)) = False
ActiveCell.FormulaR1C1 = "=VLOOKUP(R4C3,'Current Data'!A:B,2,FALSE)"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Obviously, what I am trying to do here is use a vlookup function in column G starting at row 4. For now, I'm OK with having to click on that cell before I run the macro, but assigning it a value would be nice . The vlookup function should run down the sheet, moving the lookupvalue down one row as the formula moves down. It should end when there is no value in column A.

I have two problems.
1 - The function returns a #name? value, even tho if the forumla is just entered, it works fine.
2 - I'm not sure how to make the vlookup value target cell go down one row each time as well.


Any input to send me on the correct path would be wonderful.


Thanks in advance.

`Ryan

Hi, I'm afraid there no "VLookup" but I think it does what you want:-

Please Login or Register  to view this content.

Regards Mick

Thanks a lot!

I don't mind at all that there is no vlookup, it just appeard to be the easy way to do it.


Thanks again,

`Ryan

How about vlookup in 2 data (data1 & data2 sheet) and the lookup value will post in Main sheet? whats the code for that?




Thanks,
Kooper

Dear Mick,

The below code is great but can you explain each line with comments i tried but i couldn't understand the cl and the ocl one.. the odat and rang is ok but i couldn't understand the rest pls do us a favor so all will come to know.

Originally Posted by MickG

Hi, I'm afraid there no "VLookup" but I think it does what you want:-

Please Login or Register  to view this content.

Regards Mick