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