Ok. In my situation I now have
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
which gives "=SUM(ROUND($C$7:$C$40) * ($E$7:$E$40), 2)"

How do I return that result to the range
Cells(EndToCollectionRow - 1, ColNdx) , please?

Regards.


"Tom Ogilvy" wrote in message
...
Sub Tester9()
starttocollectionRow = 1
Endtocollectionrow = 8
colndx = 5
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

returns
=SUM(ROUND(($C$1:$C$6)*($E$1:$E$6), 2))

Which appears to be something like what you want. It is a legal formula
at
least.

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
I see what you mean Tom.
Nevertheless, I cannot see the reason for that original error.

The original working code sequence was:

For ColNdx = 5 To 9 'cols E to I
Cells(EndToCollectionRow - 1, ColNdx).FormulaArray = "=SUM((" &
Range( _
Cells(StartToCollectionRow, "C"), Cells(EndToCollectionRow - 2,
"C"))
_
.Address & ")*(" & Range(Cells(StartToCollectionRow, ColNdx), _
Cells(EndToCollectionRow - 2, ColNdx)).Address & "))"
Cells(EndToCollectionRow - 1, ColNdx).Value = _
Cells(EndToCollectionRow - 1, ColNdx).Text
Next ColNdx
which produced that error of which I originally posted.

With Alex's help I tried to implement the Round function within the
above
FormulaArray expression, as follows:

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

Can this be achieved, please?

Regards.

"Tom Ogilvy" wrote in message
...
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






---
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






---
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