1

I have some experience with VBA, but usually get tripped up with syntax and such.

Here's what I'm currently trying to do that I can't figure out:

I have created an input message to ask the user what month they're looking at.

I then want to use the result of that input box in a vlookup formula and I can't seem to figure it out.

Here's the code I have so far:

Dim Mnth As String

Mnth = InputBox("Enter what month it is", "", "DO NOT ABBREVIATE")

'Open the appropriate file and tab
    Windows("Resource Tracking Kroger-MP.xlsm").Activate
    Sheets("Tables").Select
    Range("g14").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(Mnth, 'Tables'!C2:c4, 2, False)"

Here's the formula it enters into the cell using the macro above: =VLOOKUP(Mnth, Tables!$B:$D, 2, FALSE) Instead of it using the word Mnth, I need it to use the actual month

Any help is much appreciated!!!!

user3569359
  • 13
  • 4
  • `.FormulaR1C1 = "=VLOOKUP(" & Mnth & ", 'Tables'!C2:C4, 2, False)"` – Dmitry Pavliv Apr 24 '14 at 14:59
  • Thanks, Simoco. It doesn't look that that works, though. – user3569359 Apr 24 '14 at 15:36
  • `.FormulaR1C1 = "=VLOOKUP(""" & Mnth & """, 'Tables'!C2:C4, 2, False)"` – Dmitry Pavliv Apr 24 '14 at 15:38
  • Oops - sorry - posted too soon! Your code results in the following formula: =VLOOKUP(March, Tables!$B:$D, 2, FALSE), but I need it to result in the following formula: =VLOOKUP("March", Tables!$B:$D, 2, FALSE). I've tried adding extra quotation marks around & Mnth &, but it doesn't work, either. – user3569359 Apr 24 '14 at 15:38

1 Answers1

0

As I mentioned in comments, you should use:

ActiveCell.FormulaR1C1 = "=VLOOKUP(""" & Mnth & """, 'Tables'!C2:C4, 2, False)"

also read this: How to avoid using Select/Active statements

Community
  • 1
  • 1
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80