Storing formula in cell

[Cinco]

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

 

[Rick Rothstein]

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.

 

[Cinco]

Rick,

No, I want it to be executable.

Jim

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 Rothstein]

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.

 

[Cinco]

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]

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

 

[Cinco]

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]

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.

 

[Cinco]

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

 

[Cinco]

Rick,

=Sumif(S3073:S3099,"<>"&V2,R3073:R3099) with V2, for example, containing a
0 value.

Jim

 

[Rick Rothstein]

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 &")"

 

[Cinco]

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]

Sorry, I lost an ampersand along the way...

SessionLeakHrs = "=Sumif(S" & Q5 + 1 & ":S" & R5 & ",""<>" & V2 & _
""",R" & Q5 + 1 & ":R" & R5 & ")"

 

[Cinco]

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]

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.