sumproduct/evaluate

[T]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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

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

 

[Rick Rothstein \(MVP - VB\)]

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

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]

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