Vlookup not working correctly in macro
 

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

Vlookup not working correctly in macro
 

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

Vlookup not working correctly in macro
 

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

Hth,
Merjet

Vlookup not working correctly in macro
 

Nope, that's not working!

Vlookup not working correctly in macro
 

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