Ciao, Stuart.
I've read about your question on mpioe only what said us "y", not from the
beginning of thread. Now I've read the true question and I think that a loop
is not necessary. As you know, in Excel you can enter a formula on multiple
cells:
selecting the cells
writing the formula
then ending the input with Ctrl+Enter
Try so, then try it recording a macro.
So I think you can write your macro also:
With Range("E5:I5")
.FormulaR1C1 = "=ROUND(SUMPRODUCT(R1C3:R2C3,R[-4]C:R[-3]C),2)"
.Value = .Value
End With
--
Ciao :o)
Maurizio Borrelli, Microsoft MVP - Office System - Access
--------
?SPQR(C)
X
--------
"Stuart" ha scritto nel messaggio
...
Finally got the
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
Thanks both for the help.
Regards.
"y" wrote in message
...
An italian MVP suggest to me this way:
replace the
Cells(EndToCollectionRow - 1, ColNdx).FormulaLocal = ...
with
Cells(EndToCollectionRow - 1, ColNdx).Formulalocal = ...
With Cells(ETCR - 1, ColNdx)
.FormulaArray = .Formula
End With
I tried it and it works.
Hoping to hear you about other questions ;)
I don't know why you must pass through .Formulalocal
Ciao Alex.
Stuart wrote:
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.
---
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