VBA Formula Has Issue

[MikeDBMan]

I am entering this code Via VBA into a cell:

Cells(x, 4).FormulaR1C1 = "=VLOOKUP([@EEID],Manpower!A:U,18,FALSE)"

The formula gets recorded as:
=VLOOKUP([@EEID],Manpower!A: (U),18,FALSE)
(without the space after the Colon

And then evaluates to #NAME?

What is up with the () around the U and how to I avoid this issue?

 

[Kenneth Hobson]

I am not sure how it could record the []'s or the ()'s.

Try:

Cells(x, 4).FormulaR1C1 = "=VLOOKUP(" & [@EEID] & ",Manpower!A:U,18,FALSE)"

You should make sure the the formula works manually.

 

[MikeDBMan]

I didn't try your method but will. In the meantime I figured a workaround. I created a named range on the Manpower sheet that encompassed all the columns I needed. When I changed my formula to

=VLOOKUP([@EEID],MPLookUp1,18,FALSE)

it worked just fine.