Help with parameter substitution in formula (&value), vbScript
 

Hi,

I want to replace the "A40" parameter in the below formula with a
variable because this parameter should be dynamic. In short, I want
to replace the "A40" nLastRow variable. "I'm using vbScript and not
Excel's VBA. Here's the code I use:

nLastrow = objExcel.ActiveSheet.Range("B4").End(-4121).Row
objExcel.ActiveSheet.Range("D20").Formula = "=SUMIF
(A4:A40,""'5734'"",B4:B40)" ' This works.

'This fails: objExcel.ActiveSheet.Range("D"").Formula = "=SUMIF
(A4:A&nLastRow,""'5734'"",B4:B40)" ' This works.

Thanks for your help.

Help with parameter substitution in formula (&value), vbScript
 

Just a guess

..Range("D"").Formula
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"circuit_breaker" wrote in message
...
Hi,

I want to replace the "A40" parameter in the below formula with a
variable because this parameter should be dynamic. In short, I want
to replace the "A40" nLastRow variable. "I'm using vbScript and not
Excel's VBA. Here's the code I use:

nLastrow = objExcel.ActiveSheet.Range("B4").End(-4121).Row
objExcel.ActiveSheet.Range("D20").Formula = "=SUMIF
(A4:A40,""'5734'"",B4:B40)" ' This works.

'This fails: objExcel.ActiveSheet.Range("D"").Formula = "=SUMIF
(A4:A&nLastRow,""'5734'"",B4:B40)" ' This works.

Thanks for your help.