2

I cannot seem to get this to work. I want to put this vlookup formula into cell B10, looking up A10, but it gives me a NAME? value every time, as it comes out as 'A10' in the actual formula, instead of A10. Can anyone help? Here's my code:

    Range("B10").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(A10,'Sheet2'!A:B,2,0)"
reggie86
  • 279
  • 3
  • 4
  • 16
  • 4
    change `.FormulaR1C1` to `.Formula`. The `.FormulaR1C1` is looking for R1C1 notation. – Scott Craner Jun 24 '16 at 15:38
  • Also, [don't use `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). A quick fix, with @ScottCraner's suggestion, is this: `Range("B10").Formula = "=VLOOKUP(A10,'Sheet2'!A:B,2,0)"` – BruceWayne Jun 24 '16 at 15:46
  • You guys are the best-- thanks! – reggie86 Jun 24 '16 at 15:51

1 Answers1

1

as @scottCraner said, you need it in this format:

Range("B10").Select
ActiveCell.Formula = "=VLOOKUP(A10,'Sheet2'!A:B,2,0)"
Vbasic4now
  • 579
  • 3
  • 6
  • 33