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??

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)"

--
Regards,
Tom Ogilvy


"A. Karatas" wrote:

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??

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

Hth,
Merjet

Nope, that's not working!

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