|
sumproduct/evaluate
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 |
sumproduct/evaluate
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 |
sumproduct/evaluate
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 |
sumproduct/evaluate
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 |
sumproduct/evaluate
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 |
| All times are GMT +1. The time now is 10:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com