Hello.. I've tried using both of the following & neither work. Can anyone
help? The first doesn't do anything and the second returns a Value error.

For i = 1 To lstRow - 1
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
Cells(i + 1, 9).Formula = "=SUMPRODUCT(--(a1:A" & lstRow & "),--(H1:H "
& lstRow & "< 0.0215277777777778),H1: & lstrow)"

Else 'nothing

End If

Next i

For i = 1 To lstRow - 1
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
Cells(i + 1, 9).Formula = evaluate("SUMPRODUCT(--(a1:A" & lstRow &
"),--(H1:H " & lstRow & "< 0.0215277777777778),H1: & lstrow)")

Else 'nothing

End If

Next i
--
Thanks in advance!
T

You had an extra space in at the first '--(H1:H ' and were missing an H in
the last ',H1:' and had the ending parenthesis handled wrongly also. I think
this is probably what you want...

Cells(1, 1).Formula = "=SUMPRODUCT(--(a1:A" & lstrow & "),--(H1:H" & _
lstrow & "<0.0215277777777778),H1:H" & lstrow & ")"

I used the line continuation character to control the break point... you can
remove it and rejoin the two lines if you want to have the statement all on
one line.

Rick


"T" wrote in message
...
Hello.. I've tried using both of the following & neither work. Can anyone
help? The first doesn't do anything and the second returns a Value error.

For i = 1 To lstRow - 1
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
Cells(i + 1, 9).Formula = "=SUMPRODUCT(--(a1:A" & lstRow & "),--(H1:H "
& lstRow & "< 0.0215277777777778),H1: & lstrow)"

Else 'nothing

End If

Next i

For i = 1 To lstRow - 1
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
Cells(i + 1, 9).Formula = evaluate("SUMPRODUCT(--(a1:A" & lstRow &
"),--(H1:H " & lstRow & "< 0.0215277777777778),H1: & lstrow)")

Else 'nothing

End If

Next i
--
Thanks in advance!
T

Thanks Rick,

Don't know how I missed that! I do have one more problem now ... In the
first part of the formula, "=SUMPRODUCT(--(a1:A" & lstrow & " I want the
equivalent of "=SUMPRODUCT(--(a1:a" & lstrow & "=" & cells (i,1).value. In
other words (a1:a16 = a1).
The syntax I tried doesn't work.

One other question if I could... when do you need to use "evaluate"?. Only
if you need to commit the formula in Excel with control+shift+enter?

Thanks again!

--
T


"Rick Rothstein (MVP - VB)" wrote:

You had an extra space in at the first '--(H1:H ' and were missing an H in
the last ',H1:' and had the ending parenthesis handled wrongly also. I think
this is probably what you want...

Cells(1, 1).Formula = "=SUMPRODUCT(--(a1:A" & lstrow & "),--(H1:H" & _
lstrow & "<0.0215277777777778),H1:H" & lstrow & ")"

I used the line continuation character to control the break point... you can
remove it and rejoin the two lines if you want to have the statement all on
one line.

Rick


"T" wrote in message
...
Hello.. I've tried using both of the following & neither work. Can anyone
help? The first doesn't do anything and the second returns a Value error.

For i = 1 To lstRow - 1
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
Cells(i + 1, 9).Formula = "=SUMPRODUCT(--(a1:A" & lstRow & "),--(H1:H "
& lstRow & "< 0.0215277777777778),H1: & lstrow)"

Else 'nothing

End If

Next i

For i = 1 To lstRow - 1
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
Cells(i + 1, 9).Formula = evaluate("SUMPRODUCT(--(a1:A" & lstRow &
"),--(H1:H " & lstRow & "< 0.0215277777777778),H1: & lstrow)")

Else 'nothing

End If

Next i
--
Thanks in advance!
T

Is this what you are looking for?

Cells(i + 1, 9).Formula = "=SUMPRODUCT(--(A1:A" & lstRow & "=A" & i & _
"),--(H1:H" & lstRow & "<0.0215277777777778),H1:H" & lstRow & ")"

As for Evaluate... well, it evaluates an expression and produces a result...
a value (text or number) or an object (range, for one). An example (from the
Help files) would be...

trigVariable = Evaluate("SIN(45)")

which assigns 0.850903524534118 to the variable. This kind of output is not
something you would assign to the Formula property of a range.

Rick


"T" wrote in message
...
Thanks Rick,

Don't know how I missed that! I do have one more problem now ... In the
first part of the formula, "=SUMPRODUCT(--(a1:A" & lstrow & " I want the
equivalent of "=SUMPRODUCT(--(a1:a" & lstrow & "=" & cells (i,1).value.
In
other words (a1:a16 = a1).
The syntax I tried doesn't work.

One other question if I could... when do you need to use "evaluate"?.
Only
if you need to commit the formula in Excel with control+shift+enter?

Thanks again!

--
T


"Rick Rothstein (MVP - VB)" wrote:

You had an extra space in at the first '--(H1:H ' and were missing an H
in
the last ',H1:' and had the ending parenthesis handled wrongly also. I
think
this is probably what you want...

Cells(1, 1).Formula = "=SUMPRODUCT(--(a1:A" & lstrow & "),--(H1:H" & _
lstrow & "<0.0215277777777778),H1:H" & lstrow & ")"

I used the line continuation character to control the break point... you
can
remove it and rejoin the two lines if you want to have the statement all
on
one line.

Rick


"T" wrote in message
...
Hello.. I've tried using both of the following & neither work. Can
anyone
help? The first doesn't do anything and the second returns a Value
error.

For i = 1 To lstRow - 1
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
Cells(i + 1, 9).Formula = "=SUMPRODUCT(--(a1:A" & lstRow &
"),--(H1:H "
& lstRow & "< 0.0215277777777778),H1: & lstrow)"

Else 'nothing

End If

Next i

For i = 1 To lstRow - 1
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
Cells(i + 1, 9).Formula = evaluate("SUMPRODUCT(--(a1:A" & lstRow &
"),--(H1:H " & lstRow & "< 0.0215277777777778),H1: & lstrow)")

Else 'nothing

End If

Next i
--
Thanks in advance!
T

That's it. Thanks so much for your assistance, Rick!


--
T


"Rick Rothstein (MVP - VB)" wrote:

Is this what you are looking for?

Cells(i + 1, 9).Formula = "=SUMPRODUCT(--(A1:A" & lstRow & "=A" & i & _
"),--(H1:H" & lstRow & "<0.0215277777777778),H1:H" & lstRow & ")"

As for Evaluate... well, it evaluates an expression and produces a result...
a value (text or number) or an object (range, for one). An example (from the
Help files) would be...

trigVariable = Evaluate("SIN(45)")

which assigns 0.850903524534118 to the variable. This kind of output is not
something you would assign to the Formula property of a range.

Rick


"T" wrote in message
...
Thanks Rick,

Don't know how I missed that! I do have one more problem now ... In the
first part of the formula, "=SUMPRODUCT(--(a1:A" & lstrow & " I want the
equivalent of "=SUMPRODUCT(--(a1:a" & lstrow & "=" & cells (i,1).value.
In
other words (a1:a16 = a1).
The syntax I tried doesn't work.

One other question if I could... when do you need to use "evaluate"?.
Only
if you need to commit the formula in Excel with control+shift+enter?

Thanks again!

--
T


"Rick Rothstein (MVP - VB)" wrote:

You had an extra space in at the first '--(H1:H ' and were missing an H
in
the last ',H1:' and had the ending parenthesis handled wrongly also. I
think
this is probably what you want...

Cells(1, 1).Formula = "=SUMPRODUCT(--(a1:A" & lstrow & "),--(H1:H" & _
lstrow & "<0.0215277777777778),H1:H" & lstrow & ")"

I used the line continuation character to control the break point... you
can
remove it and rejoin the two lines if you want to have the statement all
on
one line.

Rick


"T" wrote in message
...
Hello.. I've tried using both of the following & neither work. Can
anyone
help? The first doesn't do anything and the second returns a Value
error.

For i = 1 To lstRow - 1
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
Cells(i + 1, 9).Formula = "=SUMPRODUCT(--(a1:A" & lstRow &
"),--(H1:H "
& lstRow & "< 0.0215277777777778),H1: & lstrow)"

Else 'nothing

End If

Next i

For i = 1 To lstRow - 1
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
Cells(i + 1, 9).Formula = evaluate("SUMPRODUCT(--(a1:A" & lstRow &
"),--(H1:H " & lstRow & "< 0.0215277777777778),H1: & lstrow)")

Else 'nothing

End If

Next i
--
Thanks in advance!
T