Need help with rounding in VBA code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

OzonedMan - 20 Aug 2007 21:38 GMT

When I try to break a value into 3 equal parts

ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[1]C/3"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=(R[2]C-R[1]C)/2"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[3]C-(R[2]C+R[1]C)"

it works fine, but how can I Round each new amount to 2 decimals for
currency?
I have some with 8 or 10 #'s after the decimal, not what I want.
kind of like
ActiveCell.FormulaR1C1 = Round("=R[1]C/3",2)
but I know that won't work.

Reply to this Message

Bob Phillips - 20 Aug 2007 22:38 GMT

With ActiveCell
.Offset(-1, 0).Range("A1").FormulaR1C1 = "=ROUND(R[1]C/3,2)"
.Offset(-1, 0).Range("A1").FormulaR1C1 = "=ROUND((R[2]C-R[1]C)/2,2)"
.Offset(-1, 0).Range("A1").FormulaR1C1 =
"=ROUND(R[3]C-(R[2]C+R[1]C),2)"
End With

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> When I try to break a value into 3 equal parts
>
[quoted text clipped - 11 lines]
> ActiveCell.FormulaR1C1 = Round("=R[1]C/3",2)
> but I know that won't work.

Reply to this Message

Don Guillett - 20 Aug 2007 22:47 GMT

Does this help

Sub doformulas()
With ActiveCell
.Offset(-1).FormulaR1C1 = "=round(R[1]C/3,2)"
.Offset(-2).FormulaR1C1 = "=round((R[2]C-R[1]C)/2,2)"
.Offset(-3).FormulaR1C1 = "=round(R[3]C-(R[2]C+R[1]C),2)"
End With
End Sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> When I try to break a value into 3 equal parts
>
[quoted text clipped - 11 lines]
> ActiveCell.FormulaR1C1 = Round("=R[1]C/3",2)
> but I know that won't work.

Reply to this Message

Barb Reinhardt - 20 Aug 2007 22:52 GMT

Try something like this

activecell.formulaR1C1="=ROUND(R[1]C/3,2)"

HTH,
Barb Reinhardt

> When I try to break a value into 3 equal parts
>
[quoted text clipped - 11 lines]
> ActiveCell.FormulaR1C1 = Round("=R[1]C/3",2)
> but I know that won't work.

Reply to this Message

gimme_this_gimme_that@yahoo.com - 21 Aug 2007 01:07 GMT

If you need a VBA solution google VBRound on this newsgroup. You'll
need to do that thing where you multiply by one hundred on the VBRound
argument and divide by one hundred on the VBRound result.

Reply to this Message