Hi,

I have a workbook that has an Input screen where I want the user to be
able to select the rounding option to be used for various cell values
throughout the workbook.

In this case, worksheet "Input" cell d35 has a drop-down that allows
the user to select rounding options of hundreds, thousands, or
millions. I want to place a value in worksheet "Output" cell c38 based
on a rounding of the value in cell b38. Cell b38 is formula-driven
within the Excel workbook.

I've tried to write some code to do this, but what I'm doing doesn't
work. When the macro is run, it puts a zero value in Output cell c38
no matter which rounding option I choose.

Does anyone have any suggestions as to how to rewrite this?

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(38, 3).Value =
Application.WorksheetFunction.Round(cells(38,2)), RoundingValue)

End Sub

The subroutine above is called from a Worksheet Change event located in
the Input worksheet as follows:

Sub Worksheet_Change(ByVal Target As Excel.Range)

' Check to see which rounding option has been chosen.
Dim RoundingValue As Integer
Set CheckRange = Range("d35")

If Not Application.Intersect(Target, CheckRange) Is Nothing
Then
Call RoundingOptions
End If

End Sub


Thanks!