WorksheetFunction.Round using user-selectable rounding value?

[Punsterr]

I'm trying to write code to round the value of cell b45 on the Output
worksheet to the number of digits specified on the Input worksheet.

RoundingValue is defined as an integer elsewhere (in a worksheet change
event).

Sub RoundingOptions()

If Sheets("Input").Cells(35, 4).Value = "Hundred Dollars" Then
RoundingValue = -2
Else
If Sheets("Input").Cells(35, 4).Value = "Thousand Dollars" Then
RoundingValue = -3
Else
If Sheets("Input").Cells(35, 4).Value = "Million Dollars"
Then
RoundingValue = -6
Else: RoundingValue = 0
End If
End If
End If

Sheets("Output").Cells(45, 3).Value =
Application.WorksheetFunction.Round(Sheets("Output").Cells(45,
2).Value, RoundingValue)


End Sub

The code above always puts a zero value in cell c43. I've checked the
value of both cell b43 and RoundingValue before executing that portion
of the code, and the values are correct... so there is a problem with
the way that I'm trying to place the new rounded value in cell c43.

Any suggestions would be appreciated!

[Bob Phillips]

Could it be that all you Rounding Values are negative, which means you will
round to the nearest 100, 1000, or 1000000. If the number is <50,
<500,<500000, you will get 0.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Punsterr" <[email protected]> wrote in message
news:[email protected]...
> I'm trying to write code to round the value of cell b45 on the Output
> worksheet to the number of digits specified on the Input worksheet.
>
> RoundingValue is defined as an integer elsewhere (in a worksheet change
> event).
>
> Sub RoundingOptions()
>
> If Sheets("Input").Cells(35, 4).Value = "Hundred Dollars" Then
> RoundingValue = -2
> Else
> If Sheets("Input").Cells(35, 4).Value = "Thousand Dollars" Then
> RoundingValue = -3
> Else
> If Sheets("Input").Cells(35, 4).Value = "Million Dollars"
> Then
> RoundingValue = -6
> Else: RoundingValue = 0
> End If
> End If
> End If
>
> Sheets("Output").Cells(45, 3).Value =
> Application.WorksheetFunction.Round(Sheets("Output").Cells(45,
> 2).Value, RoundingValue)
>
>
> End Sub
>
> The code above always puts a zero value in cell c43. I've checked the
> value of both cell b43 and RoundingValue before executing that portion
> of the code, and the values are correct... so there is a problem with
> the way that I'm trying to place the new rounded value in cell c43.
>
> Any suggestions would be appreciated!
>

[Punsterr]

Bob,

Nice catch! The sample figure I was testing it on just happened to be a
negative number. The macro does work for positive numbers. However,
I'm still not sure I'm following why the 0 value would come up for a
negative number. If I was just doing that same rounding function in an
Excel formula, it would round the negative number to the nearest
hundred/thousand/million. Any thoughts on what I need to change in my
macro to round both positive and negative numbers in the same
programming loop? (The Output sheet will have 15+ values that will be
rounded, some of which will be positive and some of which will be
negative.)

[Jerry W. Lewis]

I don't think that you and Bob are talking about the same thing. When you
say "sample figure", is sounds like you are talking about
Sheets("Output").Cells(45,
2).Value, where Bob appears to be talking about your variable named
RoundingValue.

The worksheet function ROUND() takes two arguments, number and num_digits.
Whenever ABS(number)<(10^-TRUNC(num_digits))/2, then then the result will be
zero, since number is closer to 0 than the smallest possible non-zero rounded
value. When num_digits<0, then that break point is >1, which may have snuck
up on you.

Jerry

"Punsterr" wrote:

> Bob,
>
> Nice catch! The sample figure I was testing it on just happened to be a
> negative number. The macro does work for positive numbers. However,
> I'm still not sure I'm following why the 0 value would come up for a
> negative number. If I was just doing that same rounding function in an
> Excel formula, it would round the negative number to the nearest
> hundred/thousand/million. Any thoughts on what I need to change in my
> macro to round both positive and negative numbers in the same
> programming loop? (The Output sheet will have 15+ values that will be
> rounded, some of which will be positive and some of which will be
> negative.)
>
>