I use this formula in my macro:


ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[9],ref1,2,FALSE)"

Is it possible to add code that will do the lookup in "ref1" and "ref2" ?

Thank you in advance.

VLOOKUP will stop when it finds a match, so I you have the same value in both
ranges it will only find the first.......however, you might consider wrapping
your VLOOKUP in an IF(ISNA..... formula so that if it does NOT find the
lookup value in the first range, then it will look in the second
range.......either that, or use two VLOOKUP formulas, one for each range.....

Vaya con Dios,
Chuck, CABGx3




"carl" wrote:

I use this formula in my macro:


ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[9],ref1,2,FALSE)"

Is it possible to add code that will do the lookup in "ref1" and "ref2" ?

Thank you in advance.

Some questions, so I can try to help you...

Are ref1, ref2, and ref3, named ranges in the workbook?

What is RC[9]?


--
Excel_Geek


------------------------------------------------------------------------
Excel_Geek's Profile: http://www.excelforum.com/member.php...o&userid=26423
View this thread: http://www.excelforum.com/showthread...hreadid=399200

Hi. Thank you for your help. The ref1 and ref2 are named ranges in the
workbook.

The RC[9] comes from the vba code - not sure what it is.

"Excel_Geek" wrote:


Some questions, so I can try to help you...

Are ref1, ref2, and ref3, named ranges in the workbook?

What is RC[9]?


--
Excel_Geek


------------------------------------------------------------------------
Excel_Geek's Profile: http://www.excelforum.com/member.php...o&userid=26423
View this thread: http://www.excelforum.com/showthread...hreadid=399200