Dynamic VLOOKUP formula in VBA procedure

RWayneH
RWayneH used Ask the Experts™
on
Hello
I have a need for a dynamic VLOOKUP formula within a vba procedure.
I am using, ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-37],Inventory!C[-14]:C[-5],10,FALSE)"
The dynamic part is the [-37] which refers to column A.

How would I change the formula to point to Column A, no matter the cell that the formula gets put into, so it honors the proper value being used in the VLOOKUP?
Please advise and thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Analyst Assistant
Commented:
Try this.
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Inventory!C[-14]:C[-5],10,FALSE)"

Open in new window

RWayneH

Author

Commented:
Yup, this worked!!!  Thanks for the help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial