"Rounding" error
 

Here is the data:

C F I J N O
1.17 4.45 2.79 7.24 8.47 8.47
1.23 2.12 2.12 2.61 2.61

7.81

7.81 is the sum of values in col F multiplied by their corresponding
value in col C.
So 7.81 should equal (1.17 * 4.45) + (1.23 * 2.12)
ie 5.2065 + 2.6076 = 7.8141 round to 7.81

However, I need each multiplication to round. So
1.17 * 4.45 = 5.2065 = 5.21 and
1.23 * 2.12 = 2.6076 = 2.61
thus 5.21 + 2.61 = 7.82

Here's the code that currently sums the column:

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

Is there a way to modify the code, 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

"Rounding" error
 

You could apply the ROUND statement for the sum.

round (sum();2)

I didn't test it.
Let us know.

Ciao Alex.

Stuart wrote:
Here is the data:

C F I J N O
1.17 4.45 2.79 7.24 8.47 8.47
1.23 2.12 2.12 2.61 2.61

7.81

7.81 is the sum of values in col F multiplied by their corresponding
value in col C.
So 7.81 should equal (1.17 * 4.45) + (1.23 * 2.12)
ie 5.2065 + 2.6076 = 7.8141 round to 7.81

"Rounding" error
 

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.

Regards

"y" wrote in message ...
You could apply the ROUND statement for the sum.

round (sum();2)

I didn't test it.
Let us know.

Ciao Alex.

Stuart wrote:
Here is the data:

C F I J N
O
1.17 4.45 2.79 7.24 8.47 8.47
1.23 2.12 2.12 2.61 2.61

7.81

7.81 is the sum of values in col F multiplied by their corresponding
value in col C.
So 7.81 should equal (1.17 * 4.45) + (1.23 * 2.12)
ie 5.2065 + 2.6076 = 7.8141 round to 7.81




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

"Rounding" error
 

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.

"Rounding" error
 

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

"Rounding" error
 

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

"Rounding" error
 

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

"Rounding" error
 

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.

"Rounding" error
 

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

"Rounding" error
 

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

"Rounding" error
 

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.

"Rounding" error
 

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

"Rounding" error
 

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