Hi Stuart,

I tried your expression and for the 1st step it assumes "=SUM(ROUND($C$1)*($E$1), 2)"
I think it is not correct because

you want to obtain this formula:

=SUM(ROUND(array1*array2,2)) (**)

So

1) declare a sTmp (string) variable.
2) modify the For..next statements this way:

sTmp = "=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 = sTmp

Note I added an instruction in loop

3) add a control to sTmp variable and control step by step if the sTmp assumes the correct
formula. Alternative is to insert this line of code in the loop

MsgBox sTmp

that display to you what Cells(EndToCollectionRow - 1, ColNdx).Value will be set to

4) adjust the "=SUM(ROUND.... to obtain the formula (**)

5) now it works ( ...I hope ;) )


I suggest to you to start from the final formula (id est (**) ), then substitute all fixed values
with the parameters/formulas that returns that fixed values.

1 step ) =SUM(ROUND(array1*array2,2))
2 step ) "=SUM(ROUND(" & array1*array2,2))
3 step ) "=SUM(ROUND(" & array1*array2 & ",2))"
and then the expression for array1*array2

another help is to use substrings and then concatenate them. It would reduce errors in exploding the
expression. So

sTmp1 = "=SUM(ROUND("
....

sTmp.. = ",2))"

and finally

Cells(EndToCollectionRow - 1, ColNdx).Value = sTmp & ... & sTmp..

Hope this helps.
Alex.