Excel VBA Variable in Formula

Asked Aug 07 '18 at 16:18

Active Aug 08 '18 at 08:49

Viewed 40 times

I know there are a ton of questions like this one, but I can't seem to get this working right...

I have an inputbox to define the variable "spiffmonth" and I'm trying to use it in a formula like so:

ActiveCell.FormulaR1C1 = "=VLOOKUP(R[2]C[7],RepNameKey!C:C[1],2,FALSE)" & spiffmonth

I have spiffmonth declared as string.

Can someone please help me out here? I'm banging my head against the wall.

edited Aug 08 '18 at 08:49

Pᴇʜ

asked Aug 07 '18 at 16:18

Jackson

What should the actual value of spiffmonth be? – Marcucciboy2 Aug 07 '18 at 16:20
it's an inputbox, it'll be along the format "July 2018" – Jackson Aug 07 '18 at 16:22
3

You need to have the `&` in the "'s of the first formula as your saying `vlookup(x,y,z)string` rather than `vlookup(x,y,z) & string` – Nathan_Sav Aug 07 '18 at 16:22
Include `&` in your actual formula - `ActiveCell.FormulaR1C1 = "=VLOOKUP(R[2]C[7],RepNameKey!C:C[1],2,FALSE) & " & spiffmonth` – nagarajannd Aug 07 '18 at 16:23
nagarajannd, your code produces Error 1004 – Jackson Aug 07 '18 at 16:24
1

`ActiveCell.FormulaR1C1 = "=VLOOKUP(R[2]C[7],RepNameKey!C:C[1],2,FALSE) & """ & spiffmonth & """"` – nagarajannd Aug 07 '18 at 16:31
That did the trick! Thank you Nagarajannd!!! – Jackson Aug 07 '18 at 16:41
Possible duplicate of [VBA -- variable in .formula](https://stackoverflow.com/questions/45321109/vba-variable-in-formula) – AJD Sep 02 '19 at 20:03
possible duplicate of https://stackoverflow.com/questions/45321109/vba-variable-in-formula – AJD Sep 02 '19 at 20:05

0 Answers0