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