Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Round up whole sheet

How can I roundup the whole sheet?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 622
Default Round up whole sheet

On Sep 3, 7:44 am, Daddy Dool
wrote:
How can I roundup the whole sheet?


=ROUNDUP(SUM(A:IV),2)?

Not really sure what you are asking.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,441
Default Round up whole sheet

The macro below will round up all numeric values to 0 decimal places. You can either change the
values to formulas that use the original value (what it does now), or change the value to the
rounded-up value. To do that change

myC.FormulaR1C1 = "=ROUNDUP(" & myC.Value & ",0)"
'myC.Value = Application.WorksheetFunction.RoundUp(myC.Value, 0)

to

'myC.FormulaR1C1 = "=ROUNDUP(" & myC.Value & ",0)"
myC.Value = Application.WorksheetFunction.RoundUp(myC.Value, 0)

HTH,
Bernie
MS Excel MVP


Sub RoundUpSheet()
Dim myC As Range

On Error GoTo NoFormulas
For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 1)
If InStr(1, myC.FormulaR1C1, "ROUNDUP") = 0 Then _
myC.FormulaR1C1 = "=ROUNDUP(" & Mid(myC.FormulaR1C1, 2, Len(myC.FormulaR1C1)) & ",0)"
Next myC

NoFormulas:
Resume DoValues
DoValues:
On Error GoTo NoConstants

For Each myC In Cells.SpecialCells(xlCellTypeConstants, 1)
myC.FormulaR1C1 = "=ROUNDUP(" & myC.Value & ",0)"
'myC.Value = Application.WorksheetFunction.RoundUp(myC.Value, 0)
Next myC

NoConstants:
End Sub

"Daddy Dool" wrote in message
...
How can I roundup the whole sheet?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
round up #.#0 and #.#5 ramzi New Users to Excel 6 July 22nd 08 02:27 PM
Round Function for Entire Sheet mstjohn New Users to Excel 1 January 16th 08 06:06 AM
round down? dbrumit Excel Discussion (Misc queries) 1 February 15th 07 05:35 PM
How do I ROUND() round off decimals of a column dataset? Højrup Excel Worksheet Functions 2 January 12th 05 11:50 AM
round up ? dammit1 Excel Worksheet Functions 4 November 10th 04 05:09 PM


All times are GMT +1. The time now is 02:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"