Re: "Rounding" error

From: Maurizio Borrelli (maurizio.borrelli_at_freepass.it)
Date: 04/12/04 

Date: Mon, 12 Apr 2004 05:46:21 +0200

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" <sg_booth@hotmail.com> ha scritto nel messaggio
news:uz3OgWwHEHA.3748@tk2msftngp13.phx.gbl...
> Finally got there:
> 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" <y@y.y> wrote in message
news:%23ox$jmnHEHA.2924@TK2MSFTNGP09.phx.gbl...
> > 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
>
>