Rounding Up 1

[SomeYahoo]

Is there a way (conditional formatting perhaps?) to automatically round up an input decimal number to the next integer (i.e. 24.1 automatically becomes 25)? I know you can do this with the ROUNDUP function, but you would then have to hide the actual input cell.

Just wondering if there was a shortcut. Thanks!

 

[SomeYahoo]

I have done a small abount of VB in the past, but it would take me a long time to get re-spun. Thanks anyway PEStructural.

 

[PEStructural]

In case you were interested, this is the code that would be used:

Sub auto_open()

' Run the macro DidCellsChange any time a entry is made in a
' cell in Sheet1.
ThisWorkbook.Worksheets("Sheet1").OnEntry = "DidCellsChange"

End Sub


Sub DidCellsChange()
Dim KeyCells As String
' Define which cells should trigger the KeyCellsChanged macro.
KeyCells = "E11"

' If the Activecell is one of the key cells, call the
' KeyCellsChanged macro.
If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
Is Nothing Then KeyCellsChanged

End Sub

Sub KeyCellsChanged()
'
Application.ScreenUpdating = False 'Turning off screen
'
' Copying original value
Range("E11").Select
Selection.Copy
' Pasting original value
Range("G11").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
' Rounding up original value
Range("E11") = Application.WorksheetFunction.RoundUp(Worksheets("Sheet1").Range("E11"), 0)
Range("E11").Select
'
Application.ScreenUpdating = True
'
End Sub

You would just have to change the E11 and G11 to whatever your cells are. To copy this into Excel VB, go to Tools>Macro>Visual Basic Editor. On the left side of the screen you'll see a box named Project - VBAProject. In that box you'll see something that says VBAProject (Filename.xls). The filename.xls would be the actual file you're using, not "filename". Then you right click on VBAProject (Filename.xls), then click Insert>Module. Then in the module, copy the above code into it and hit the save button on the top of the screen. Then exit the Visual Basic Editor.

Save your worksheet and exit Excel. Open the spreadsheet back up and click Enable Macros. Now if you input a number into E11 (or whatever you changed it to), it will copy the original input number to G11 and roundup the value in E11.

Hope this helps,

PEStructural

 

[lilliput1]

Why don't you just hide the rounded output cells & display only the input. Or you can put the output in another worksheet. Protect your formulas.

 

[brengine]

I'm with lilliput1, just have an input column and a ROUNDED-UP column. The formula for rounding up cell A1 would be:
=CEILING(A1,1)

Now just show/hide the columns at will. If you need more visual clues then you could have the rounded up column with larger font, different color font, or highlight the cells, or etc...

Ken

 

[SomeYahoo]

Thanks for all the input... I was just wondering if there was an easy way with formatting, etc. I ended up just hiding cells.

 

[iken]

To get the tool bar to show, right click anywhere on the toolbar (at the top). Tick the formating toolbar. This should then show.

The button I mentioned has two lines. The top with an arrow next to a dot and one 0, the second with an arrow next to a dot and two 0's.

One mirrored for increasing decimal places.