Vlookup not working correctly in macro

[A. Karatas]

The following is a part of a macro I use

a = 2
Range("ec" & a).Select
While Not IsEmpty(Range("ec" & a).Offset(0, -132).Value) '<> ""
Range("ec" & a).Value = "=vlookup(v" & a & " ,markets!
R3c1:r66c3,3,false)"
Range("ed" & a).Value = "=+Assumptions!R1C4"
Range("ee" & a).Value = "=vlookup(s" & a & " ,factory!
R2c2:r5000c26,24,false)"
Range("ef" & a).Value = "=if(bg" & a & "+bi" & a & "+z" & a &
"+ab" & a & "+du" & a & "+dw" & a & "=0,0,1)"
Range("DU" & a).Value = "=CN" & a & "-DY" & a
Range("DV" & a).Value = "=CO" & a & "-DZ" & a
Range("DW" & a).Value = "=CP" & a & "-EA" & a
a = a + 1

Wend

If the macro is finished with its work the Vlookup in excel cannot be
calculated because the lookup shows the following.
=VLOOKUP('v2';Markets!$A$3:$C$66;3;FALSE). In other words the macro
adds ' between my search value v2 ??? how to solve the problem??

 

[Tom Ogilvy]

It is because your formula has mixed R1C1 and A1 notation.

Range("ec" & a).Value = "=vlookup(v" & a & "
,markets!R3c1:r66c3,3,false)"


should be

Range("ec" & a).formulaR1C1 = "=vlookup(R" & a & "C22
,markets!R3c1:r66c3,3,false)"

 

[merjet]

Try using ".FormulaR1C1" instead of ".Value".

Hth,
Merjet

 

[A. Karatas]

Nope, that's not working!

 

[merjet]

Another problem with your vlookup formulas is using mixed references
-- A1 for the 1st argument and R1C1 for the 2nd. There are two
options:
- FormulaR1C1 with R1C1 references for both arguments
- Formula with A1 references for both arguments.

Hth,
Merjet