We prepare conceptual cost estimates for architectural
clients, and frequently do unit cost math in a cell. I
would like to program a macro to round the calculated
value appropriate to its value, e.g.:

<$1 Nearest penny $.51
<$10 Nearest dollar $9.00
<$100 Nearest ten $90.00
$1000 TBD

I've programmed in Access but have only used the Macro
recorder in Excel. Can someone tell me how to construct
an appropriate Switch statement?

Kevin Sprinkel

Kevin,

Something like

If myvalue < 1 Then
myvalue = Round(myvalue, 2)
ElseIf myvalue < 10 Then
myvalue = Round(myvalue, 0)
ElseIf myvalue < 100 Then
myvalue = Round(myvalue / 10, 0) * 10
Else
'TBD
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Kevin Sprinkel" wrote in message
...
We prepare conceptual cost estimates for architectural
clients, and frequently do unit cost math in a cell. I
would like to program a macro to round the calculated
value appropriate to its value, e.g.:

<$1 Nearest penny $.51
<$10 Nearest dollar $9.00
<$100 Nearest ten $90.00
$1000 TBD

I've programmed in Access but have only used the Macro
recorder in Excel. Can someone tell me how to construct
an appropriate Switch statement?

Kevin Sprinkel

Thanks, Bob.

-----Original Message-----
Kevin,

Something like

If myvalue < 1 Then
myvalue = Round(myvalue, 2)
ElseIf myvalue < 10 Then
myvalue = Round(myvalue, 0)
ElseIf myvalue < 100 Then
myvalue = Round(myvalue / 10, 0) * 10
Else
'TBD
End If

--

HTH

Bob Phillips

http://groups.google.com/groups?selm...08%40erols.com

gives a formula for rounding to a specified number of significant figures.

Jerry

Kevin Sprinkel wrote:

We prepare conceptual cost estimates for architectural
clients, and frequently do unit cost math in a cell. I
would like to program a macro to round the calculated
value appropriate to its value, e.g.:

<$1 Nearest penny $.51
<$10 Nearest dollar $9.00
<$100 Nearest ten $90.00

$1000 TBD


I've programmed in Access but have only used the Macro
recorder in Excel. Can someone tell me how to construct
an appropriate Switch statement?

Kevin Sprinkel