Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Storing formula in cell
I want to save the following formula in a cell:
=Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5))" I've tried using double "s at various places but with no success. The formula that is saved does not match the one above. Any ideas on what the right combination of " is for this formula so I can save a copy in a cell? Thanks. Jim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Storing formula in cell
When you save "save in a cell"... do you mean as text so it doesn't get
evaluated? If yes, just leave it as it is and put an apostrophe in front of the equal sign. -- Rick (MVP - Excel) "Cinco" wrote in message ... I want to save the following formula in a cell: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5))" I've tried using double "s at various places but with no success. The formula that is saved does not match the one above. Any ideas on what the right combination of " is for this formula so I can save a copy in a cell? Thanks. Jim |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Storing formula in cell
Rick,
No, I want it to be executable. Jim "Rick Rothstein" wrote: When you save "save in a cell"... do you mean as text so it doesn't get evaluated? If yes, just leave it as it is and put an apostrophe in front of the equal sign. -- Rick (MVP - Excel) "Cinco" wrote in message ... I want to save the following formula in a cell: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5))" I've tried using double "s at various places but with no success. The formula that is saved does not match the one above. Any ideas on what the right combination of " is for this formula so I can save a copy in a cell? Thanks. Jim |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Storing formula in cell
Your originally posted formula looks like it has an extra quote mark at the
very end... try removing the last character (which is a quote mark) and see if that does it for you. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, No, I want it to be executable. Jim "Rick Rothstein" wrote: When you save "save in a cell"... do you mean as text so it doesn't get evaluated? If yes, just leave it as it is and put an apostrophe in front of the equal sign. -- Rick (MVP - Excel) "Cinco" wrote in message ... I want to save the following formula in a cell: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5))" I've tried using double "s at various places but with no success. The formula that is saved does not match the one above. Any ideas on what the right combination of " is for this formula so I can save a copy in a cell? Thanks. Jim |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Storing formula in cell
Rick,
Here is a test macro with the formula that I am trying to save in a cell. The formula needs to be executable in the cell not just a text string. I can't get it to work - I tried replacing all the quotes in the formula with double quotes but it still wouldn't work. In this example, I am showing the actual real formula without any double quotes. Give it a try and see if you can figure out what needs to be done. Maybe it is too complicated a formula to save!! Jim Sub TestStoreFormula() ' Initialize Night Begin/End Date/Time cells/values NightStartDateNTime = "=$B$2+$A$1" Range("$B$1").Select ActiveCell.Formula = NightStartDateNTime NightEndDateNTime = "=$B$1+0.99929" Range("$C$2").Select ActiveCell.Formula = NightEndDateNTime ' TestStoreFormula Macro ' Macro recorded 7/24/2009 ' Need different formula for calculating Leak Hrs for Session Charts than for Night Charts SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" Range("$W$2").Select ActiveCell.Formula = SessionLeakHrs End Sub "Rick Rothstein" wrote: Your originally posted formula looks like it has an extra quote mark at the very end... try removing the last character (which is a quote mark) and see if that does it for you. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, No, I want it to be executable. Jim "Rick Rothstein" wrote: When you save "save in a cell"... do you mean as text so it doesn't get evaluated? If yes, just leave it as it is and put an apostrophe in front of the equal sign. -- Rick (MVP - Excel) "Cinco" wrote in message ... I want to save the following formula in a cell: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5))" I've tried using double "s at various places but with no success. The formula that is saved does not match the one above. Any ideas on what the right combination of " is for this formula so I can save a copy in a cell? Thanks. Jim |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Storing formula in cell
SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<"
&V2,Indirect("R" &Q5+1 &":R" &R5+1))" There are a couple of ways to interpret exactly which formula you want from the above. Given the set up you show in the statement above, can you show us sample values for Q5, R5 and V5 and then show us the formula you want to end up in the cell for those given values? -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, Here is a test macro with the formula that I am trying to save in a cell. The formula needs to be executable in the cell not just a text string. I can't get it to work - I tried replacing all the quotes in the formula with double quotes but it still wouldn't work. In this example, I am showing the actual real formula without any double quotes. Give it a try and see if you can figure out what needs to be done. Maybe it is too complicated a formula to save!! Jim Sub TestStoreFormula() ' Initialize Night Begin/End Date/Time cells/values NightStartDateNTime = "=$B$2+$A$1" Range("$B$1").Select ActiveCell.Formula = NightStartDateNTime NightEndDateNTime = "=$B$1+0.99929" Range("$C$2").Select ActiveCell.Formula = NightEndDateNTime ' TestStoreFormula Macro ' Macro recorded 7/24/2009 ' Need different formula for calculating Leak Hrs for Session Charts than for Night Charts SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" Range("$W$2").Select ActiveCell.Formula = SessionLeakHrs End Sub "Rick Rothstein" wrote: Your originally posted formula looks like it has an extra quote mark at the very end... try removing the last character (which is a quote mark) and see if that does it for you. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, No, I want it to be executable. Jim "Rick Rothstein" wrote: When you save "save in a cell"... do you mean as text so it doesn't get evaluated? If yes, just leave it as it is and put an apostrophe in front of the equal sign. -- Rick (MVP - Excel) "Cinco" wrote in message ... I want to save the following formula in a cell: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5))" I've tried using double "s at various places but with no success. The formula that is saved does not match the one above. Any ideas on what the right combination of " is for this formula so I can save a copy in a cell? Thanks. Jim |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Storing formula in cell
Rick,
I don't understand your question. I want the formula =SUMIF(Indirect ....)) to appear in the cell as a formula as if I had entered it manually in the cell (which is what I initially did). I need two different formulas that differ only in that one uses a reference to &R5+1 and one uses a reference to &R5. I thought that I could use a macro to save the proper formula in the cell depending on which one I need. All my processing dynamically selects the correct range. Q5 is the starting row # of the range, R5 is the ending row number of the range, and V2 (for example, may = 0) All I want to do is put a formula in a cell just as if I had entered it manually. Thanks for working on this. I appreciate you trying to figure this out. Jim "Rick Rothstein" wrote: SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" There are a couple of ways to interpret exactly which formula you want from the above. Given the set up you show in the statement above, can you show us sample values for Q5, R5 and V5 and then show us the formula you want to end up in the cell for those given values? -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, Here is a test macro with the formula that I am trying to save in a cell. The formula needs to be executable in the cell not just a text string. I can't get it to work - I tried replacing all the quotes in the formula with double quotes but it still wouldn't work. In this example, I am showing the actual real formula without any double quotes. Give it a try and see if you can figure out what needs to be done. Maybe it is too complicated a formula to save!! Jim Sub TestStoreFormula() ' Initialize Night Begin/End Date/Time cells/values NightStartDateNTime = "=$B$2+$A$1" Range("$B$1").Select ActiveCell.Formula = NightStartDateNTime NightEndDateNTime = "=$B$1+0.99929" Range("$C$2").Select ActiveCell.Formula = NightEndDateNTime ' TestStoreFormula Macro ' Macro recorded 7/24/2009 ' Need different formula for calculating Leak Hrs for Session Charts than for Night Charts SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" Range("$W$2").Select ActiveCell.Formula = SessionLeakHrs End Sub "Rick Rothstein" wrote: Your originally posted formula looks like it has an extra quote mark at the very end... try removing the last character (which is a quote mark) and see if that does it for you. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, No, I want it to be executable. Jim "Rick Rothstein" wrote: When you save "save in a cell"... do you mean as text so it doesn't get evaluated? If yes, just leave it as it is and put an apostrophe in front of the equal sign. -- Rick (MVP - Excel) "Cinco" wrote in message ... I want to save the following formula in a cell: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5))" I've tried using double "s at various places but with no success. The formula that is saved does not match the one above. Any ideas on what the right combination of " is for this formula so I can save a copy in a cell? Thanks. Jim |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Storing formula in cell
What I want you to do was show us the formula you expect our code to produce
for you. Don't show us "Indirect ....", we see that part, show us what the .... part is. Here, do this. Make believe Q5=2, R5=7 and V2=9... what formula do you want our code to put in the cell for those values (show us the exact formula)? I do have a reason for asking for this. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, I don't understand your question. I want the formula =SUMIF(Indirect ....)) to appear in the cell as a formula as if I had entered it manually in the cell (which is what I initially did). I need two different formulas that differ only in that one uses a reference to &R5+1 and one uses a reference to &R5. I thought that I could use a macro to save the proper formula in the cell depending on which one I need. All my processing dynamically selects the correct range. Q5 is the starting row # of the range, R5 is the ending row number of the range, and V2 (for example, may = 0) All I want to do is put a formula in a cell just as if I had entered it manually. Thanks for working on this. I appreciate you trying to figure this out. Jim "Rick Rothstein" wrote: SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" There are a couple of ways to interpret exactly which formula you want from the above. Given the set up you show in the statement above, can you show us sample values for Q5, R5 and V5 and then show us the formula you want to end up in the cell for those given values? -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, Here is a test macro with the formula that I am trying to save in a cell. The formula needs to be executable in the cell not just a text string. I can't get it to work - I tried replacing all the quotes in the formula with double quotes but it still wouldn't work. In this example, I am showing the actual real formula without any double quotes. Give it a try and see if you can figure out what needs to be done. Maybe it is too complicated a formula to save!! Jim Sub TestStoreFormula() ' Initialize Night Begin/End Date/Time cells/values NightStartDateNTime = "=$B$2+$A$1" Range("$B$1").Select ActiveCell.Formula = NightStartDateNTime NightEndDateNTime = "=$B$1+0.99929" Range("$C$2").Select ActiveCell.Formula = NightEndDateNTime ' TestStoreFormula Macro ' Macro recorded 7/24/2009 ' Need different formula for calculating Leak Hrs for Session Charts than for Night Charts SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" Range("$W$2").Select ActiveCell.Formula = SessionLeakHrs End Sub "Rick Rothstein" wrote: Your originally posted formula looks like it has an extra quote mark at the very end... try removing the last character (which is a quote mark) and see if that does it for you. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, No, I want it to be executable. Jim "Rick Rothstein" wrote: When you save "save in a cell"... do you mean as text so it doesn't get evaluated? If yes, just leave it as it is and put an apostrophe in front of the equal sign. -- Rick (MVP - Excel) "Cinco" wrote in message ... I want to save the following formula in a cell: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5))" I've tried using double "s at various places but with no success. The formula that is saved does not match the one above. Any ideas on what the right combination of " is for this formula so I can save a copy in a cell? Thanks. Jim |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Storing formula in cell
Rick,
Here is the formula that I included in my first post: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5)) What it would look like for a starting row value of Q5 = 3072, ending row value of R5 = 3099, and a leak value of V2 = 0 would be, I think, the following: =Sumif(S3073:S3099 < 0,R3073:R3099) While waiting to find out how to store the above formula (and its twin using &R5+1) in a cell via a macro statement, I manually entered each version in its own cell. My macro selects the cell with the correct formula result and copies that value into a common cell used in my calculations. Problem solved! I'm still curious on how to store the above "complicated" formula (since it contains lots of ") in a cell as an executable formula similar to one that I have entered manually. Jim "Rick Rothstein" wrote: What I want you to do was show us the formula you expect our code to produce for you. Don't show us "Indirect ....", we see that part, show us what the .... part is. Here, do this. Make believe Q5=2, R5=7 and V2=9... what formula do you want our code to put in the cell for those values (show us the exact formula)? I do have a reason for asking for this. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, I don't understand your question. I want the formula =SUMIF(Indirect ....)) to appear in the cell as a formula as if I had entered it manually in the cell (which is what I initially did). I need two different formulas that differ only in that one uses a reference to &R5+1 and one uses a reference to &R5. I thought that I could use a macro to save the proper formula in the cell depending on which one I need. All my processing dynamically selects the correct range. Q5 is the starting row # of the range, R5 is the ending row number of the range, and V2 (for example, may = 0) All I want to do is put a formula in a cell just as if I had entered it manually. Thanks for working on this. I appreciate you trying to figure this out. Jim "Rick Rothstein" wrote: SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" There are a couple of ways to interpret exactly which formula you want from the above. Given the set up you show in the statement above, can you show us sample values for Q5, R5 and V5 and then show us the formula you want to end up in the cell for those given values? -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, Here is a test macro with the formula that I am trying to save in a cell. The formula needs to be executable in the cell not just a text string. I can't get it to work - I tried replacing all the quotes in the formula with double quotes but it still wouldn't work. In this example, I am showing the actual real formula without any double quotes. Give it a try and see if you can figure out what needs to be done. Maybe it is too complicated a formula to save!! Jim Sub TestStoreFormula() ' Initialize Night Begin/End Date/Time cells/values NightStartDateNTime = "=$B$2+$A$1" Range("$B$1").Select ActiveCell.Formula = NightStartDateNTime NightEndDateNTime = "=$B$1+0.99929" Range("$C$2").Select ActiveCell.Formula = NightEndDateNTime ' TestStoreFormula Macro ' Macro recorded 7/24/2009 ' Need different formula for calculating Leak Hrs for Session Charts than for Night Charts SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" Range("$W$2").Select ActiveCell.Formula = SessionLeakHrs End Sub "Rick Rothstein" wrote: Your originally posted formula looks like it has an extra quote mark at the very end... try removing the last character (which is a quote mark) and see if that does it for you. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, No, I want it to be executable. Jim "Rick Rothstein" wrote: When you save "save in a cell"... do you mean as text so it doesn't get evaluated? If yes, just leave it as it is and put an apostrophe in front of the equal sign. -- Rick (MVP - Excel) "Cinco" wrote in message ... I want to save the following formula in a cell: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5))" I've tried using double "s at various places but with no success. The formula that is saved does not match the one above. Any ideas on what the right combination of " is for this formula so I can save a copy in a cell? Thanks. Jim |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Storing formula in cell
Rick,
=Sumif(S3073:S3099,"<"&V2,R3073:R3099) with V2, for example, containing a 0 value. Jim "Cinco" wrote: Rick, Here is the formula that I included in my first post: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5)) What it would look like for a starting row value of Q5 = 3072, ending row value of R5 = 3099, and a leak value of V2 = 0 would be, I think, the following: =Sumif(S3073:S3099 < 0,R3073:R3099) While waiting to find out how to store the above formula (and its twin using &R5+1) in a cell via a macro statement, I manually entered each version in its own cell. My macro selects the cell with the correct formula result and copies that value into a common cell used in my calculations. Problem solved! I'm still curious on how to store the above "complicated" formula (since it contains lots of ") in a cell as an executable formula similar to one that I have entered manually. Jim "Rick Rothstein" wrote: What I want you to do was show us the formula you expect our code to produce for you. Don't show us "Indirect ....", we see that part, show us what the .... part is. Here, do this. Make believe Q5=2, R5=7 and V2=9... what formula do you want our code to put in the cell for those values (show us the exact formula)? I do have a reason for asking for this. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, I don't understand your question. I want the formula =SUMIF(Indirect ....)) to appear in the cell as a formula as if I had entered it manually in the cell (which is what I initially did). I need two different formulas that differ only in that one uses a reference to &R5+1 and one uses a reference to &R5. I thought that I could use a macro to save the proper formula in the cell depending on which one I need. All my processing dynamically selects the correct range. Q5 is the starting row # of the range, R5 is the ending row number of the range, and V2 (for example, may = 0) All I want to do is put a formula in a cell just as if I had entered it manually. Thanks for working on this. I appreciate you trying to figure this out. Jim "Rick Rothstein" wrote: SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" There are a couple of ways to interpret exactly which formula you want from the above. Given the set up you show in the statement above, can you show us sample values for Q5, R5 and V5 and then show us the formula you want to end up in the cell for those given values? -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, Here is a test macro with the formula that I am trying to save in a cell. The formula needs to be executable in the cell not just a text string. I can't get it to work - I tried replacing all the quotes in the formula with double quotes but it still wouldn't work. In this example, I am showing the actual real formula without any double quotes. Give it a try and see if you can figure out what needs to be done. Maybe it is too complicated a formula to save!! Jim Sub TestStoreFormula() ' Initialize Night Begin/End Date/Time cells/values NightStartDateNTime = "=$B$2+$A$1" Range("$B$1").Select ActiveCell.Formula = NightStartDateNTime NightEndDateNTime = "=$B$1+0.99929" Range("$C$2").Select ActiveCell.Formula = NightEndDateNTime ' TestStoreFormula Macro ' Macro recorded 7/24/2009 ' Need different formula for calculating Leak Hrs for Session Charts than for Night Charts SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" Range("$W$2").Select ActiveCell.Formula = SessionLeakHrs End Sub "Rick Rothstein" wrote: Your originally posted formula looks like it has an extra quote mark at the very end... try removing the last character (which is a quote mark) and see if that does it for you. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, No, I want it to be executable. Jim "Rick Rothstein" wrote: When you save "save in a cell"... do you mean as text so it doesn't get evaluated? If yes, just leave it as it is and put an apostrophe in front of the equal sign. -- Rick (MVP - Excel) "Cinco" wrote in message ... I want to save the following formula in a cell: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5))" I've tried using double "s at various places but with no success. The formula that is saved does not match the one above. Any ideas on what the right combination of " is for this formula so I can save a copy in a cell? Thanks. Jim |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Storing formula in cell
The reason I wanted to see what you expected the final formula to look like
is I didn't think you really wanted the Indirect function call in it (although there was a possible construction that would have required it, so I needed you to confirm what you expected to see in the end). Here is the line of code you were looking for... SessionLeakHrs = "=Sumif(S" & Q5+1 & ":S" & R5 ",""<" & V2 & _ """,R" &Q5+1 & ":R" & R5 &")" -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, Here is the formula that I included in my first post: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5)) What it would look like for a starting row value of Q5 = 3072, ending row value of R5 = 3099, and a leak value of V2 = 0 would be, I think, the following: =Sumif(S3073:S3099 < 0,R3073:R3099) While waiting to find out how to store the above formula (and its twin using &R5+1) in a cell via a macro statement, I manually entered each version in its own cell. My macro selects the cell with the correct formula result and copies that value into a common cell used in my calculations. Problem solved! I'm still curious on how to store the above "complicated" formula (since it contains lots of ") in a cell as an executable formula similar to one that I have entered manually. Jim "Rick Rothstein" wrote: What I want you to do was show us the formula you expect our code to produce for you. Don't show us "Indirect ....", we see that part, show us what the .... part is. Here, do this. Make believe Q5=2, R5=7 and V2=9... what formula do you want our code to put in the cell for those values (show us the exact formula)? I do have a reason for asking for this. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, I don't understand your question. I want the formula =SUMIF(Indirect ....)) to appear in the cell as a formula as if I had entered it manually in the cell (which is what I initially did). I need two different formulas that differ only in that one uses a reference to &R5+1 and one uses a reference to &R5. I thought that I could use a macro to save the proper formula in the cell depending on which one I need. All my processing dynamically selects the correct range. Q5 is the starting row # of the range, R5 is the ending row number of the range, and V2 (for example, may = 0) All I want to do is put a formula in a cell just as if I had entered it manually. Thanks for working on this. I appreciate you trying to figure this out. Jim "Rick Rothstein" wrote: SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" There are a couple of ways to interpret exactly which formula you want from the above. Given the set up you show in the statement above, can you show us sample values for Q5, R5 and V5 and then show us the formula you want to end up in the cell for those given values? -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, Here is a test macro with the formula that I am trying to save in a cell. The formula needs to be executable in the cell not just a text string. I can't get it to work - I tried replacing all the quotes in the formula with double quotes but it still wouldn't work. In this example, I am showing the actual real formula without any double quotes. Give it a try and see if you can figure out what needs to be done. Maybe it is too complicated a formula to save!! Jim Sub TestStoreFormula() ' Initialize Night Begin/End Date/Time cells/values NightStartDateNTime = "=$B$2+$A$1" Range("$B$1").Select ActiveCell.Formula = NightStartDateNTime NightEndDateNTime = "=$B$1+0.99929" Range("$C$2").Select ActiveCell.Formula = NightEndDateNTime ' TestStoreFormula Macro ' Macro recorded 7/24/2009 ' Need different formula for calculating Leak Hrs for Session Charts than for Night Charts SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" Range("$W$2").Select ActiveCell.Formula = SessionLeakHrs End Sub "Rick Rothstein" wrote: Your originally posted formula looks like it has an extra quote mark at the very end... try removing the last character (which is a quote mark) and see if that does it for you. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, No, I want it to be executable. Jim "Rick Rothstein" wrote: When you save "save in a cell"... do you mean as text so it doesn't get evaluated? If yes, just leave it as it is and put an apostrophe in front of the equal sign. -- Rick (MVP - Excel) "Cinco" wrote in message ... I want to save the following formula in a cell: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5))" I've tried using double "s at various places but with no success. The formula that is saved does not match the one above. Any ideas on what the right combination of " is for this formula so I can save a copy in a cell? Thanks. Jim |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Storing formula in cell
Rick,
Your line of code didn't work. Received a syntax error (I tried entering it by hand and then resorted to a cut&paste). I suspect that your line of code is either missing something or has something extra: space, ", or whatever. Jim "Rick Rothstein" wrote: The reason I wanted to see what you expected the final formula to look like is I didn't think you really wanted the Indirect function call in it (although there was a possible construction that would have required it, so I needed you to confirm what you expected to see in the end). Here is the line of code you were looking for... SessionLeakHrs = "=Sumif(S" & Q5+1 & ":S" & R5 ",""<" & V2 & _ """,R" &Q5+1 & ":R" & R5 &")" -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, Here is the formula that I included in my first post: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5)) What it would look like for a starting row value of Q5 = 3072, ending row value of R5 = 3099, and a leak value of V2 = 0 would be, I think, the following: =Sumif(S3073:S3099 < 0,R3073:R3099) While waiting to find out how to store the above formula (and its twin using &R5+1) in a cell via a macro statement, I manually entered each version in its own cell. My macro selects the cell with the correct formula result and copies that value into a common cell used in my calculations. Problem solved! I'm still curious on how to store the above "complicated" formula (since it contains lots of ") in a cell as an executable formula similar to one that I have entered manually. Jim "Rick Rothstein" wrote: What I want you to do was show us the formula you expect our code to produce for you. Don't show us "Indirect ....", we see that part, show us what the .... part is. Here, do this. Make believe Q5=2, R5=7 and V2=9... what formula do you want our code to put in the cell for those values (show us the exact formula)? I do have a reason for asking for this. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, I don't understand your question. I want the formula =SUMIF(Indirect ....)) to appear in the cell as a formula as if I had entered it manually in the cell (which is what I initially did). I need two different formulas that differ only in that one uses a reference to &R5+1 and one uses a reference to &R5. I thought that I could use a macro to save the proper formula in the cell depending on which one I need. All my processing dynamically selects the correct range. Q5 is the starting row # of the range, R5 is the ending row number of the range, and V2 (for example, may = 0) All I want to do is put a formula in a cell just as if I had entered it manually. Thanks for working on this. I appreciate you trying to figure this out. Jim "Rick Rothstein" wrote: SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" There are a couple of ways to interpret exactly which formula you want from the above. Given the set up you show in the statement above, can you show us sample values for Q5, R5 and V5 and then show us the formula you want to end up in the cell for those given values? -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, Here is a test macro with the formula that I am trying to save in a cell. The formula needs to be executable in the cell not just a text string. I can't get it to work - I tried replacing all the quotes in the formula with double quotes but it still wouldn't work. In this example, I am showing the actual real formula without any double quotes. Give it a try and see if you can figure out what needs to be done. Maybe it is too complicated a formula to save!! Jim Sub TestStoreFormula() ' Initialize Night Begin/End Date/Time cells/values NightStartDateNTime = "=$B$2+$A$1" Range("$B$1").Select ActiveCell.Formula = NightStartDateNTime NightEndDateNTime = "=$B$1+0.99929" Range("$C$2").Select ActiveCell.Formula = NightEndDateNTime ' TestStoreFormula Macro ' Macro recorded 7/24/2009 ' Need different formula for calculating Leak Hrs for Session Charts than for Night Charts SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" Range("$W$2").Select ActiveCell.Formula = SessionLeakHrs End Sub "Rick Rothstein" wrote: Your originally posted formula looks like it has an extra quote mark at the very end... try removing the last character (which is a quote mark) and see if that does it for you. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, No, I want it to be executable. Jim "Rick Rothstein" wrote: When you save "save in a cell"... do you mean as text so it doesn't get evaluated? If yes, just leave it as it is and put an apostrophe in front of the equal sign. -- Rick (MVP - Excel) "Cinco" wrote in message ... I want to save the following formula in a cell: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5))" I've tried using double "s at various places but with no success. The formula that is saved does not match the one above. Any ideas on what the right combination of " is for this formula so I can save a copy in a cell? Thanks. Jim |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Storing formula in cell
Sorry, I lost an ampersand along the way...
SessionLeakHrs = "=Sumif(S" & Q5 + 1 & ":S" & R5 & ",""<" & V2 & _ """,R" & Q5 + 1 & ":R" & R5 & ")" -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, Your line of code didn't work. Received a syntax error (I tried entering it by hand and then resorted to a cut&paste). I suspect that your line of code is either missing something or has something extra: space, ", or whatever. Jim "Rick Rothstein" wrote: The reason I wanted to see what you expected the final formula to look like is I didn't think you really wanted the Indirect function call in it (although there was a possible construction that would have required it, so I needed you to confirm what you expected to see in the end). Here is the line of code you were looking for... SessionLeakHrs = "=Sumif(S" & Q5+1 & ":S" & R5 ",""<" & V2 & _ """,R" &Q5+1 & ":R" & R5 &")" -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, Here is the formula that I included in my first post: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5)) What it would look like for a starting row value of Q5 = 3072, ending row value of R5 = 3099, and a leak value of V2 = 0 would be, I think, the following: =Sumif(S3073:S3099 < 0,R3073:R3099) While waiting to find out how to store the above formula (and its twin using &R5+1) in a cell via a macro statement, I manually entered each version in its own cell. My macro selects the cell with the correct formula result and copies that value into a common cell used in my calculations. Problem solved! I'm still curious on how to store the above "complicated" formula (since it contains lots of ") in a cell as an executable formula similar to one that I have entered manually. Jim "Rick Rothstein" wrote: What I want you to do was show us the formula you expect our code to produce for you. Don't show us "Indirect ....", we see that part, show us what the .... part is. Here, do this. Make believe Q5=2, R5=7 and V2=9... what formula do you want our code to put in the cell for those values (show us the exact formula)? I do have a reason for asking for this. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, I don't understand your question. I want the formula =SUMIF(Indirect ....)) to appear in the cell as a formula as if I had entered it manually in the cell (which is what I initially did). I need two different formulas that differ only in that one uses a reference to &R5+1 and one uses a reference to &R5. I thought that I could use a macro to save the proper formula in the cell depending on which one I need. All my processing dynamically selects the correct range. Q5 is the starting row # of the range, R5 is the ending row number of the range, and V2 (for example, may = 0) All I want to do is put a formula in a cell just as if I had entered it manually. Thanks for working on this. I appreciate you trying to figure this out. Jim "Rick Rothstein" wrote: SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" There are a couple of ways to interpret exactly which formula you want from the above. Given the set up you show in the statement above, can you show us sample values for Q5, R5 and V5 and then show us the formula you want to end up in the cell for those given values? -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, Here is a test macro with the formula that I am trying to save in a cell. The formula needs to be executable in the cell not just a text string. I can't get it to work - I tried replacing all the quotes in the formula with double quotes but it still wouldn't work. In this example, I am showing the actual real formula without any double quotes. Give it a try and see if you can figure out what needs to be done. Maybe it is too complicated a formula to save!! Jim Sub TestStoreFormula() ' Initialize Night Begin/End Date/Time cells/values NightStartDateNTime = "=$B$2+$A$1" Range("$B$1").Select ActiveCell.Formula = NightStartDateNTime NightEndDateNTime = "=$B$1+0.99929" Range("$C$2").Select ActiveCell.Formula = NightEndDateNTime ' TestStoreFormula Macro ' Macro recorded 7/24/2009 ' Need different formula for calculating Leak Hrs for Session Charts than for Night Charts SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" Range("$W$2").Select ActiveCell.Formula = SessionLeakHrs End Sub "Rick Rothstein" wrote: Your originally posted formula looks like it has an extra quote mark at the very end... try removing the last character (which is a quote mark) and see if that does it for you. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, No, I want it to be executable. Jim "Rick Rothstein" wrote: When you save "save in a cell"... do you mean as text so it doesn't get evaluated? If yes, just leave it as it is and put an apostrophe in front of the equal sign. -- Rick (MVP - Excel) "Cinco" wrote in message ... I want to save the following formula in a cell: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5))" I've tried using double "s at various places but with no success. The formula that is saved does not match the one above. Any ideas on what the right combination of " is for this formula so I can save a copy in a cell? Thanks. Jim |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Storing formula in cell
Rick,
Your latest code didn't generate a syntax error but what it stored in the cell has no resemblance whatsoever to the original formula. If you care to continue this dialogue, please send me your test code that produces the desired result. Thanks. Jim "Rick Rothstein" wrote: Sorry, I lost an ampersand along the way... SessionLeakHrs = "=Sumif(S" & Q5 + 1 & ":S" & R5 & ",""<" & V2 & _ """,R" & Q5 + 1 & ":R" & R5 & ")" -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, Your line of code didn't work. Received a syntax error (I tried entering it by hand and then resorted to a cut&paste). I suspect that your line of code is either missing something or has something extra: space, ", or whatever. Jim "Rick Rothstein" wrote: The reason I wanted to see what you expected the final formula to look like is I didn't think you really wanted the Indirect function call in it (although there was a possible construction that would have required it, so I needed you to confirm what you expected to see in the end). Here is the line of code you were looking for... SessionLeakHrs = "=Sumif(S" & Q5+1 & ":S" & R5 ",""<" & V2 & _ """,R" &Q5+1 & ":R" & R5 &")" -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, Here is the formula that I included in my first post: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5)) What it would look like for a starting row value of Q5 = 3072, ending row value of R5 = 3099, and a leak value of V2 = 0 would be, I think, the following: =Sumif(S3073:S3099 < 0,R3073:R3099) While waiting to find out how to store the above formula (and its twin using &R5+1) in a cell via a macro statement, I manually entered each version in its own cell. My macro selects the cell with the correct formula result and copies that value into a common cell used in my calculations. Problem solved! I'm still curious on how to store the above "complicated" formula (since it contains lots of ") in a cell as an executable formula similar to one that I have entered manually. Jim "Rick Rothstein" wrote: What I want you to do was show us the formula you expect our code to produce for you. Don't show us "Indirect ....", we see that part, show us what the .... part is. Here, do this. Make believe Q5=2, R5=7 and V2=9... what formula do you want our code to put in the cell for those values (show us the exact formula)? I do have a reason for asking for this. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, I don't understand your question. I want the formula =SUMIF(Indirect ....)) to appear in the cell as a formula as if I had entered it manually in the cell (which is what I initially did). I need two different formulas that differ only in that one uses a reference to &R5+1 and one uses a reference to &R5. I thought that I could use a macro to save the proper formula in the cell depending on which one I need. All my processing dynamically selects the correct range. Q5 is the starting row # of the range, R5 is the ending row number of the range, and V2 (for example, may = 0) All I want to do is put a formula in a cell just as if I had entered it manually. Thanks for working on this. I appreciate you trying to figure this out. Jim "Rick Rothstein" wrote: SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" There are a couple of ways to interpret exactly which formula you want from the above. Given the set up you show in the statement above, can you show us sample values for Q5, R5 and V5 and then show us the formula you want to end up in the cell for those given values? -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, Here is a test macro with the formula that I am trying to save in a cell. The formula needs to be executable in the cell not just a text string. I can't get it to work - I tried replacing all the quotes in the formula with double quotes but it still wouldn't work. In this example, I am showing the actual real formula without any double quotes. Give it a try and see if you can figure out what needs to be done. Maybe it is too complicated a formula to save!! Jim Sub TestStoreFormula() ' Initialize Night Begin/End Date/Time cells/values NightStartDateNTime = "=$B$2+$A$1" Range("$B$1").Select ActiveCell.Formula = NightStartDateNTime NightEndDateNTime = "=$B$1+0.99929" Range("$C$2").Select ActiveCell.Formula = NightEndDateNTime ' TestStoreFormula Macro ' Macro recorded 7/24/2009 ' Need different formula for calculating Leak Hrs for Session Charts than for Night Charts SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" Range("$W$2").Select ActiveCell.Formula = SessionLeakHrs End Sub "Rick Rothstein" wrote: Your originally posted formula looks like it has an extra quote mark at the very end... try removing the last character (which is a quote mark) and see if that does it for you. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, No, I want it to be executable. Jim "Rick Rothstein" wrote: When you save "save in a cell"... do you mean as text so it doesn't get evaluated? If yes, just leave it as it is and put an apostrophe in front of the equal sign. -- Rick (MVP - Excel) "Cinco" wrote in message ... I want to save the following formula in a cell: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5))" I've tried using double "s at various places but with no success. The formula that is saved does not match the one above. Any ideas on what the right combination of " is for this formula so I can save a copy in a cell? Thanks. Jim |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Storing formula in cell
I don't have "test code"... what I am trying to do is give you code to
produce the formula you want to have. I see where I **may** have misunderstood what you wanted in the last posting where you offered this formula... =Sumif(S3073:S3099,"<"&V2,R3073:R3099) and wrote this after it... "with V2, for example, containing a 0 value". I took that to mean you wanted the value of V2 embedded into the formula instead of just referencing V2 within the formula itself. But if that is the error in interpretation I have made, I don't see where it has "no resemblance whatsoever to the original formula". Here is the code to produce EXACTLY the formula above when Q5 = 3072 and R5 = 3099 (what's in V2 is immaterial)... SessionLeakHrs = "=Sumif(S" & Q5 + 1 & ":S" & R5 & ",""<""&V2" & _ ",R" & Q5 + 1 & ":R" & R5 & ")" Now, if this doesn't do what you want, then all I am (and have been) asking you to do is show me what formula you are expecting to see in the cell for those values of Q5, R5 and (maybe) V2. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, Your latest code didn't generate a syntax error but what it stored in the cell has no resemblance whatsoever to the original formula. If you care to continue this dialogue, please send me your test code that produces the desired result. Thanks. Jim "Rick Rothstein" wrote: Sorry, I lost an ampersand along the way... SessionLeakHrs = "=Sumif(S" & Q5 + 1 & ":S" & R5 & ",""<" & V2 & _ """,R" & Q5 + 1 & ":R" & R5 & ")" -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, Your line of code didn't work. Received a syntax error (I tried entering it by hand and then resorted to a cut&paste). I suspect that your line of code is either missing something or has something extra: space, ", or whatever. Jim "Rick Rothstein" wrote: The reason I wanted to see what you expected the final formula to look like is I didn't think you really wanted the Indirect function call in it (although there was a possible construction that would have required it, so I needed you to confirm what you expected to see in the end). Here is the line of code you were looking for... SessionLeakHrs = "=Sumif(S" & Q5+1 & ":S" & R5 ",""<" & V2 & _ """,R" &Q5+1 & ":R" & R5 &")" -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, Here is the formula that I included in my first post: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5)) What it would look like for a starting row value of Q5 = 3072, ending row value of R5 = 3099, and a leak value of V2 = 0 would be, I think, the following: =Sumif(S3073:S3099 < 0,R3073:R3099) While waiting to find out how to store the above formula (and its twin using &R5+1) in a cell via a macro statement, I manually entered each version in its own cell. My macro selects the cell with the correct formula result and copies that value into a common cell used in my calculations. Problem solved! I'm still curious on how to store the above "complicated" formula (since it contains lots of ") in a cell as an executable formula similar to one that I have entered manually. Jim "Rick Rothstein" wrote: What I want you to do was show us the formula you expect our code to produce for you. Don't show us "Indirect ....", we see that part, show us what the .... part is. Here, do this. Make believe Q5=2, R5=7 and V2=9... what formula do you want our code to put in the cell for those values (show us the exact formula)? I do have a reason for asking for this. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, I don't understand your question. I want the formula =SUMIF(Indirect ....)) to appear in the cell as a formula as if I had entered it manually in the cell (which is what I initially did). I need two different formulas that differ only in that one uses a reference to &R5+1 and one uses a reference to &R5. I thought that I could use a macro to save the proper formula in the cell depending on which one I need. All my processing dynamically selects the correct range. Q5 is the starting row # of the range, R5 is the ending row number of the range, and V2 (for example, may = 0) All I want to do is put a formula in a cell just as if I had entered it manually. Thanks for working on this. I appreciate you trying to figure this out. Jim "Rick Rothstein" wrote: SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" There are a couple of ways to interpret exactly which formula you want from the above. Given the set up you show in the statement above, can you show us sample values for Q5, R5 and V5 and then show us the formula you want to end up in the cell for those given values? -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, Here is a test macro with the formula that I am trying to save in a cell. The formula needs to be executable in the cell not just a text string. I can't get it to work - I tried replacing all the quotes in the formula with double quotes but it still wouldn't work. In this example, I am showing the actual real formula without any double quotes. Give it a try and see if you can figure out what needs to be done. Maybe it is too complicated a formula to save!! Jim Sub TestStoreFormula() ' Initialize Night Begin/End Date/Time cells/values NightStartDateNTime = "=$B$2+$A$1" Range("$B$1").Select ActiveCell.Formula = NightStartDateNTime NightEndDateNTime = "=$B$1+0.99929" Range("$C$2").Select ActiveCell.Formula = NightEndDateNTime ' TestStoreFormula Macro ' Macro recorded 7/24/2009 ' Need different formula for calculating Leak Hrs for Session Charts than for Night Charts SessionLeakHrs = "=SumIf(Indirect("S" &Q5+1 &":S" &R5+1),"<" &V2,Indirect("R" &Q5+1 &":R" &R5+1))" Range("$W$2").Select ActiveCell.Formula = SessionLeakHrs End Sub "Rick Rothstein" wrote: Your originally posted formula looks like it has an extra quote mark at the very end... try removing the last character (which is a quote mark) and see if that does it for you. -- Rick (MVP - Excel) "Cinco" wrote in message ... Rick, No, I want it to be executable. Jim "Rick Rothstein" wrote: When you save "save in a cell"... do you mean as text so it doesn't get evaluated? If yes, just leave it as it is and put an apostrophe in front of the equal sign. -- Rick (MVP - Excel) "Cinco" wrote in message ... I want to save the following formula in a cell: =Sumif(Indirect("S" &Q5+1 &":S" &R5),"<" &V2,Indirect("R" &Q5+1 &":R" &R5))" I've tried using double "s at various places but with no success. The formula that is saved does not match the one above. Any ideas on what the right combination of " is for this formula so I can save a copy in a cell? Thanks. Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Storing an array in a cell | Excel Worksheet Functions | |||
Storing a range variable in a cell | Excel Discussion (Misc queries) | |||
storing lettrs in an excel cell to later = a number for a formula | Excel Discussion (Misc queries) | |||
excel storing previous cell values in memory | Excel Worksheet Functions | |||
Storing Formulas to Use | Excel Worksheet Functions |