VBA help with Vlookup

**sgaurav** · 03-06-2011, 04:41 AM

Hello folks,
I am a novice with VBA programming in Excel.

I am trying to do a simple VLOOKUP of Sheet1, Column B Values in Sheet2,Column1
As you can see from attachment VLOOKUP function in Excel but I am unable to get this simple thing done using a macro.

Please advise/help.

Thanks,
Gaurav

**TMShucks** · 03-06-2011, 04:52 AM

Not sure I understand what you want to do with a macro but:

    Range("C2").FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R1C1:R6C1,1,FALSE)"

Or:

    Range("C2:C11").FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R1C1:R6C1,1,FALSE)"

Regards

**sgaurav** · 03-06-2011, 05:13 AM

Thanks !
I have recorded a similar macro but the problem comes when my column positions are not fixed.

It would be really helpful, if some code snippet can be shared which does not use R1C1 format.

-Gaurav

**royUK** · 03-06-2011, 05:31 AM

Why do you need VBA to add the formula?

**barryleajo** · 03-06-2011, 06:01 AM

Hi sgaurav
Welcome to the forum
As Roy says it may be a sledgehammer to crack a nut but .. you're the customer!

I have attached a probably lengthy solution (loop within a loop) but if it gives you ideas and takes you forward with your VBA coding then its worth it.
Roy and others can make this more efficient and more elegant.
barry

**sgaurav** · 03-06-2011, 08:42 PM

Barry - Thanks !
This helps.

Roy,
I have to perform some repititive data analysis. The task and data format is fixed but not the data.
Hence I tried by creating a simple dummy excel to see how VLookup query will be coded in macro which I can finally extend to real data.

Thanks,
Gaurav

**icon123** · 03-18-2011, 03:06 PM

Originally Posted by barryleajo

Hi sgaurav
Welcome to the forum
As Roy says it may be a sledgehammer to crack a nut but .. you're the customer!

I have attached a probably lengthy solution (loop within a loop) but if it gives you ideas and takes you forward with your VBA coding then its worth it.
Roy and others can make this more efficient and more elegant.
barry

This is very similar to what I was looking for. But how would the code change to reference the look up table being in a seperate workbook? Can the workbook remain closed? Thanks.