Round up entire worksheet
 

Is there a way to make it so every number entered in a worksheet is
automatically rounded up? My work wants the numbers rounded up (12.01
rounded up to 13.00), however they want the actually amount to be typed in
for possible future audits.

I don't have to do this cell by cell do I?

Round up entire worksheet
 

The only way to do that is to use 2 cells. One for the actual amount and one
for the rounded amount.

A1 = 12.01

B1 FORMULA: =CEILING(A1,1)

Biff

"Jaime" wrote in message
...
Is there a way to make it so every number entered in a worksheet is
automatically rounded up? My work wants the numbers rounded up (12.01
rounded up to 13.00), however they want the actually amount to be typed in
for possible future audits.

I don't have to do this cell by cell do I?

Round up entire worksheet
 

Quick and easy way to do it - create the sheet as you normally would, using
actual values. Format, etc, per usual. Then create a copy of the sheet in
the same workbook (Right click the sheet tab and choose Move or copy-make
sure to click the Create a Copy check box) and in the new sheet, replace the
values with a reference to the cells from the original sheet using the
ceiling function. Then you have everything the bosses want without double
the work.
If your sheets were named Actual and Rounded and cell B3 in the actual sheet
contained a value, then the formula in cell B3 in the Rounded sheet would be:
=Ceiling(Actual!B3,1) - once you have the first, you can use AutoFill to
copy to adjacent cells.
Good luck!

"Jaime" wrote:

Is there a way to make it so every number entered in a worksheet is
automatically rounded up? My work wants the numbers rounded up (12.01
rounded up to 13.00), however they want the actually amount to be typed in
for possible future audits.

I don't have to do this cell by cell do I?

Round up entire worksheet
 

Hi!
Thank you for the info. Very helpful. One question - what does the number
after the comma stand for in the formula Ex. =ceiling(A1,1)?
--
Nancy :)


"T. Valko" wrote:

The only way to do that is to use 2 cells. One for the actual amount and one
for the rounded amount.

A1 = 12.01

B1 FORMULA: =CEILING(A1,1)

Biff

"Jaime" wrote in message
...
Is there a way to make it so every number entered in a worksheet is
automatically rounded up? My work wants the numbers rounded up (12.01
rounded up to 13.00), however they want the actually amount to be typed in
for possible future audits.

I don't have to do this cell by cell do I?

Round up entire worksheet
 

what does the number after the comma stand for in the
formula Ex. =ceiling(A1,1)?

That means to roundup to the next increment of 1.

12.01 rounds up to 13

A1 = 33

=CEILING(A1,10)

Rounds up to the next increment of 10. Result = 40

=CEILING(A1,5)

Rounds up to the next increment of 5. Result = 35

Biff

"Nancy" wrote in message
...
Hi!
Thank you for the info. Very helpful. One question - what does the
number
after the comma stand for in the formula Ex. =ceiling(A1,1)?
--
Nancy :)


"T. Valko" wrote:

The only way to do that is to use 2 cells. One for the actual amount and
one
for the rounded amount.

A1 = 12.01

B1 FORMULA: =CEILING(A1,1)

Biff

"Jaime" wrote in message
...
Is there a way to make it so every number entered in a worksheet is
automatically rounded up? My work wants the numbers rounded up (12.01
rounded up to 13.00), however they want the actually amount to be typed
in
for possible future audits.

I don't have to do this cell by cell do I?

Round up entire worksheet
 

If you have questions about Excel functions, it's (almost) always worth
looking in Excel help. As well as an explanation, it will usually give
examples, and also related functions. As far as I am aware, DATEDIF is the
only function not included in help.
--
David Biddulph

"Nancy" wrote in message
...
Hi!
Thank you for the info. Very helpful. One question - what does the
number
after the comma stand for in the formula Ex. =ceiling(A1,1)?

"T. Valko" wrote:

The only way to do that is to use 2 cells. One for the actual amount and
one
for the rounded amount.

A1 = 12.01

B1 FORMULA: =CEILING(A1,1)

Biff

"Jaime" wrote in message
...
Is there a way to make it so every number entered in a worksheet is
automatically rounded up? My work wants the numbers rounded up (12.01
rounded up to 13.00), however they want the actually amount to be typed
in
for possible future audits.

I don't have to do this cell by cell do I?

Round up entire worksheet
 

The workbook is full of various sheets that are compiled from many
organizations...and go to the state. I cannot create additional sheets.

I did look in help-and it did not tell me how to round an entire worksheet
up, that's why I came here. I just want to type a number in, like 123.49 and
have it automatically jump up to 124.

I take it there is no way to do this? Using the currency option works,
except it rounds down if it's .49 or less :( I want all numbers to round up
to the next whole dollar.

Thank you.

"BoniM" wrote:

Quick and easy way to do it - create the sheet as you normally would, using
actual values. Format, etc, per usual. Then create a copy of the sheet in
the same workbook (Right click the sheet tab and choose Move or copy-make
sure to click the Create a Copy check box) and in the new sheet, replace the
values with a reference to the cells from the original sheet using the
ceiling function. Then you have everything the bosses want without double
the work.
If your sheets were named Actual and Rounded and cell B3 in the actual sheet
contained a value, then the formula in cell B3 in the Rounded sheet would be:
=Ceiling(Actual!B3,1) - once you have the first, you can use AutoFill to
copy to adjacent cells.
Good luck!

"Jaime" wrote:

Is there a way to make it so every number entered in a worksheet is
automatically rounded up? My work wants the numbers rounded up (12.01
rounded up to 13.00), however they want the actually amount to be typed in
for possible future audits.

I don't have to do this cell by cell do I?

Round up entire worksheet
 

You can use the following code to add the ceiling function to range of values:

Sub RndUpRng()
Dim ColumnCount As Integer
Dim RowCount As Integer
For RowCount = 1 To Selection.Rows.Count
For ColumnCount = 1 To Selection.Columns.Count
ActiveCell = "=Ceiling(" & (ActiveCell) & ",1)"
If ColumnCount < Selection.Columns.Count Then
ActiveCell.Offset(0, 1).Activate
Else
ActiveCell.Offset(1, -Selection.Columns.Count + 1).Activate
End If
Next ColumnCount
Next RowCount
End Sub

Select only cells that contain values, not formulas, or the formulas will be
converted to their current value. If you don't know how to enter or use
code, see:
http://www.mvps.org/dmcritchie/excel....htm#havemacro
instructions by David McRitchie (Microsoft MVP)

"Christina" wrote:

The workbook is full of various sheets that are compiled from many
organizations...and go to the state. I cannot create additional sheets.

I did look in help-and it did not tell me how to round an entire worksheet
up, that's why I came here. I just want to type a number in, like 123.49 and
have it automatically jump up to 124.

I take it there is no way to do this? Using the currency option works,
except it rounds down if it's .49 or less :( I want all numbers to round up
to the next whole dollar.

Thank you.

"BoniM" wrote:

Quick and easy way to do it - create the sheet as you normally would, using
actual values. Format, etc, per usual. Then create a copy of the sheet in
the same workbook (Right click the sheet tab and choose Move or copy-make
sure to click the Create a Copy check box) and in the new sheet, replace the
values with a reference to the cells from the original sheet using the
ceiling function. Then you have everything the bosses want without double
the work.
If your sheets were named Actual and Rounded and cell B3 in the actual sheet
contained a value, then the formula in cell B3 in the Rounded sheet would be:
=Ceiling(Actual!B3,1) - once you have the first, you can use AutoFill to
copy to adjacent cells.
Good luck!

"Jaime" wrote:

Is there a way to make it so every number entered in a worksheet is
automatically rounded up? My work wants the numbers rounded up (12.01
rounded up to 13.00), however they want the actually amount to be typed in
for possible future audits.

I don't have to do this cell by cell do I?

Round entire worksheet
 

To Boni,
Your code works great to round multiple rows and columns. However, I am trying to add code to then copy paste special values only to the code and have not been successful. I have tried adding the lines of code under the rounding function and have tried a separate subroutine. Neither works. Can you help me make this work? Here's the code as edited:

Dim ColumnCount As Integer
Dim RowCount As Integer
For RowCount = 1 To Selection.Rows.Count
For ColumnCount = 1 To Selection.Columns.Count
ActiveCell = "=Round(" & (ActiveCell) & ",0)"

' ADDED CODE_________________________________
ActiveCell.Copy
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
' END ADDED CODE____________________________________________

If ColumnCount < Selection.Columns.Count Then
ActiveCell.Offset(0, 1).Activate
Else
ActiveCell.Offset(1, -Selection.Columns.Count + 1).Activate
End If
Next ColumnCount
Next RowCount


For RowCount = 1 To Selection.Rows.Count
For ColumnCount = 1 To Selection.Columns.Count
'ActiveCell = "=Round(" & (ActiveCell) & ",0)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
If ColumnCount < Selection.Columns.Count Then
ActiveCell.Offset(0, 1).Activate
Else
ActiveCell.Offset(1, -Selection.Columns.Count + 1).Activate
End If
Next ColumnCount
Next RowCount



Boni wrote:

You can use the following code to add the ceiling function to range of
25-Apr-07

You can use the following code to add the ceiling function to range of values:

Sub RndUpRng()
Dim ColumnCount As Integer
Dim RowCount As Integer
For RowCount = 1 To Selection.Rows.Count
For ColumnCount = 1 To Selection.Columns.Count
ActiveCell = "=Ceiling(" & (ActiveCell) & ",1)"
If ColumnCount < Selection.Columns.Count Then
ActiveCell.Offset(0, 1).Activate
Else
ActiveCell.Offset(1, -Selection.Columns.Count + 1).Activate
End If
Next ColumnCount
Next RowCount
End Sub

Select only cells that contain values, not formulas, or the formulas will be
converted to their current value. If you don't know how to enter or use
code, see:
http://www.mvps.org/dmcritchie/excel....htm#havemacro
instructions by David McRitchie (Microsoft MVP)

"Christina" wrote:

Previous Posts In This Thread:

On Monday, April 23, 2007 10:38 PM
Jaim wrote:

Round up entire worksheet
Is there a way to make it so every number entered in a worksheet is
automatically rounded up? My work wants the numbers rounded up (12.01
rounded up to 13.00), however they want the actually amount to be typed in
for possible future audits.

I don't have to do this cell by cell do I?

On Monday, April 23, 2007 11:02 PM
T. Valko wrote:

The only way to do that is to use 2 cells.
The only way to do that is to use 2 cells. One for the actual amount and one
for the rounded amount.

A1 = 12.01

B1 FORMULA: =CEILING(A1,1)

Biff

On Monday, April 23, 2007 11:50 PM
Boni wrote:

Quick and easy way to do it - create the sheet as you normally would, using
Quick and easy way to do it - create the sheet as you normally would, using
actual values. Format, etc, per usual. Then create a copy of the sheet in
the same workbook (Right click the sheet tab and choose Move or copy-make
sure to click the Create a Copy check box) and in the new sheet, replace the
values with a reference to the cells from the original sheet using the
ceiling function. Then you have everything the bosses want without double
the work.
If your sheets were named Actual and Rounded and cell B3 in the actual sheet
contained a value, then the formula in cell B3 in the Rounded sheet would be:
=Ceiling(Actual!B3,1) - once you have the first, you can use AutoFill to
copy to adjacent cells.
Good luck!

"Jaime" wrote:

On Tuesday, April 24, 2007 11:24 AM
Nanc wrote:

Hi!
Hi!
Thank you for the info. Very helpful. One question - what does the number
after the comma stand for in the formula Ex. =ceiling(A1,1)?
--
Nancy :)


"T. Valko" wrote:

On Tuesday, April 24, 2007 1:36 PM
T. Valko wrote:

That means to roundup to the next increment of 1.12.
That means to roundup to the next increment of 1.

12.01 rounds up to 13

A1 = 33

=CEILING(A1,10)

Rounds up to the next increment of 10. Result = 40

=CEILING(A1,5)

Rounds up to the next increment of 5. Result = 35

Biff

"Nancy" wrote in message
...

On Tuesday, April 24, 2007 2:16 PM
David Biddulph wrote:

If you have questions about Excel functions, it's (almost) always worth
If you have questions about Excel functions, it's (almost) always worth
looking in Excel help. As well as an explanation, it will usually give
examples, and also related functions. As far as I am aware, DATEDIF is the
only function not included in help.
--
David Biddulph

"Nancy" wrote in message
...

On Tuesday, April 24, 2007 3:04 PM
Christin wrote:

The workbook is full of various sheets that are compiled from many
The workbook is full of various sheets that are compiled from many
organizations...and go to the state. I cannot create additional sheets.

I did look in help-and it did not tell me how to round an entire worksheet
up, that's why I came here. I just want to type a number in, like 123.49 and
have it automatically jump up to 124.

I take it there is no way to do this? Using the currency option works,
except it rounds down if it's .49 or less :( I want all numbers to round up
to the next whole dollar.

Thank you.

"BoniM" wrote:

On Wednesday, April 25, 2007 12:40 AM
Boni wrote:

You can use the following code to add the ceiling function to range of
You can use the following code to add the ceiling function to range of values:

Sub RndUpRng()
Dim ColumnCount As Integer
Dim RowCount As Integer
For RowCount = 1 To Selection.Rows.Count
For ColumnCount = 1 To Selection.Columns.Count
ActiveCell = "=Ceiling(" & (ActiveCell) & ",1)"
If ColumnCount < Selection.Columns.Count Then
ActiveCell.Offset(0, 1).Activate
Else
ActiveCell.Offset(1, -Selection.Columns.Count + 1).Activate
End If
Next ColumnCount
Next RowCount
End Sub

Select only cells that contain values, not formulas, or the formulas will be
converted to their current value. If you don't know how to enter or use
code, see:
http://www.mvps.org/dmcritchie/excel....htm#havemacro
instructions by David McRitchie (Microsoft MVP)

"Christina" wrote:

EggHeadCafe - Software Developer Portal of Choice
Custom Cached Server-side XML Scrolling News
http://www.eggheadcafe.com/tutorials...erverside.aspx

Round up entire worksheet
 

thank you