What kind of testing have you done Stuart?
Sub Tester9()
starttocollectionRow = 1
Endtocollectionrow = 8
colndx = 3
sStr = "=SUM(ROUND(" & Range( _
Cells(starttocollectionRow, "C"), Cells(Endtocollectionrow - 2, "C")) _
.Address & ")*(" & Range(Cells(starttocollectionRow, colndx), _
Cells(Endtocollectionrow - 2, colndx)).Address & "), 2)"
Debug.Print sStr
End Sub
produces
=SUM(ROUND($C$1:$C$6)*($C$1:$C$6), 2)
so you have clearly miscoded it since this formula is in error.
--
Regards,
Tom Ogilvy
"Stuart" wrote in message
...
Have amended to:
For ColNdx = 5 To 9 'cols E to I
Cells(EndToCollectionRow - 1, ColNdx).FormulaArray = "=SUM(ROUND(" &
Range( _
Cells(StartToCollectionRow, "C"), Cells(EndToCollectionRow - 2, "C"))
_
.Address & ")*(" & Range(Cells(StartToCollectionRow, ColNdx), _
Cells(EndToCollectionRow - 2, ColNdx)).Address & "), 2)"
Cells(EndToCollectionRow - 1, ColNdx).Value = _
Cells(EndToCollectionRow - 1, ColNdx).Text
Next ColNdx
and get "Unable to set the FormulaArray property of the Range class"
error.
Did I miscode your suggestion?
Regards and thanks.
"y" wrote in message
...
Stuart wrote:
Thanks, but I think the chance to Round has passed by the time
the value hits the Sum cell.
I guess I need to find a way to Round each calculation BEFORE
its' result is added to the Sum....if you see what I mean.
Sorry Stuart,
round globally the product this way
....formularray = SUM(ROUND(array1*array2;roundoff))
Choose the round off you desire.
I tested this formula manually in a worksheeet and Excel allows it.
Let us know.
Alex.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004