I need to programmatically set up a vlookup for a column starting at row 11 column G. The lookup range will be on sheet2 row 2-65535 and columns A:B. The issue is that it needs to be dynamic as the lookup range will be increased as new items are added when new vendors are added.

This is a smaller part of a large solution and once the lookup is run I need column G to be converted to a non-lookup column or just a Values column. This sheet is being read in by another program and the formula is throwing it off.

I will be calling the code from a menu item so that part I can do. I am on a time limit here or I would be able to do more of it myself.

So how to modify the lookup source range to include all items when a new one is added?

Thanks guys

VB Code:
  1. 'Select column G row 11 - End

  2.     Workbooks(1).Sheets("Sheet1").Range("G11:G65535").Select

  3.     'Apply formula for vlookup - this already does rows 2 - 13 and columns 1 - 2 on sheet2. needs extending

  4.     Workbooks(1).Sheets("Sheet1").Range.FormulaR1C1 = "=VLOOKUP(RC[11]:R[11]C[11],Sheet2!R[-9]C[-6]:R[2]C[-5],2,TRUE)"