writing a formula using VBA
 

I am trying to make a macro that will write a formula to a cell. I am using
the code below but running into an issue with it.

ActiveCell.Formula =
"=SUMPRODUCT(SUBTOTAL(3,OFFSET(L6:L2000,ROW(L6:L20 00)-MIN(ROW(L6:L2000)),,1))*(L6:L2000="Yes"))"

it acts like all of it is going to work until it gets to the ="Yes" part of
the code and then it expects the first " to be the end of the formula. How
else can I get a macro to write
=SUMPRODUCT(SUBTOTAL(3,OFFSET(L6:L2000,ROW(L6:L200 0)-MIN(ROW(L6:L2000)),,1))*(L6:L2000="Yes")) into cell B2?

Thank you for your help.
Greg

writing a formula using VBA
 

Hi Greg,

For quotes qithin quotes, use two quotes

"=SUMPRODUCT(SUBTOTAL(3,OFFSET(L6:L2000,ROW(L6:L20 00)-MIN(ROW(L6:L2000)),,1))*(L6:L2000=""Yes""))"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Greg" wrote in message
...
I am trying to make a macro that will write a formula to a cell. I am
using
the code below but running into an issue with it.

ActiveCell.Formula =
"=SUMPRODUCT(SUBTOTAL(3,OFFSET(L6:L2000,ROW(L6:L20 00)-MIN(ROW(L6:L2000)),,1))*(L6:L2000="Yes"))"

it acts like all of it is going to work until it gets to the ="Yes" part
of
the code and then it expects the first " to be the end of the formula.
How
else can I get a macro to write
=SUMPRODUCT(SUBTOTAL(3,OFFSET(L6:L2000,ROW(L6:L200 0)-MIN(ROW(L6:L2000)),,1))*(L6:L2000="Yes"))
into cell B2?

Thank you for your help.
Greg

writing a formula using VBA
 

HI Greg

Double the quotes around text:
...))*(L6:L2000=""Yes""))"

HTH. Best wishes Harald

"Greg" wrote in message
...
I am trying to make a macro that will write a formula to a cell. I am
using
the code below but running into an issue with it.

ActiveCell.Formula =
"=SUMPRODUCT(SUBTOTAL(3,OFFSET(L6:L2000,ROW(L6:L20 00)-MIN(ROW(L6:L2000)),,1))*(L6:L2000="Yes"))"

it acts like all of it is going to work until it gets to the ="Yes" part
of
the code and then it expects the first " to be the end of the formula.
How
else can I get a macro to write
=SUMPRODUCT(SUBTOTAL(3,OFFSET(L6:L2000,ROW(L6:L200 0)-MIN(ROW(L6:L2000)),,1))*(L6:L2000="Yes"))
into cell B2?

Thank you for your help.
Greg

writing a formula using VBA
 

Thank you both so much. I am not sure why I did not think of that. Worked
great.

Greg