Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing a formula using VBA
Thank you both so much. I am not sure why I did not think of that. Worked
great. Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with writing formula. | Excel Worksheet Functions | |||
Writing own formula 2 | Excel Programming | |||
Need help writing formula plz.. | Excel Discussion (Misc queries) | |||
Need Help Writing a Formula | Excel Worksheet Functions | |||
Help writing a formula | Excel Worksheet Functions |