Results 1 to 15 of 27

20050613, 22:33 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Inserting Certain Operations (2000)
I have a very large worksheet that contains many (>250) formulas that contain both hard data (such as 125*D3), as well as links to other worksheets/files. What I would like to do is "apply", say, a rounding convention to all of the cells with formulas, such that each formula is rounded to the nearest whole number. How can I do this without going into and editing each cell?
Thanks.

20050613, 22:43 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Inserting Certain Operations (2000)
You can create a macro to handle this. The macro doesn't have to be stored in the workbook itself, it can be in any workbook. Select a range containing the cells with formulas before running the macro (it may also contain cells with constant values and empty cells).
Sub RoundFormulas
Dim oCell As Range
For Each oCell In Selection.Cells
If oCell.HasFormula Then
oCell.Formula = "=ROUND(" & Mid(oCell.Formula, 2) & ",0)"
End If
Next oCell
Set oCell = Nothing
End Sub

20050613, 23:12 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Inserting Certain Operations (2000)
I would recommend:
<pre>If oCell.HasFormula _
<font color=red>and left(ocell.formula, 7) <> "=ROUND(" </font color=red> Then
oCell.Formula = "=ROUND(" & Mid(oCell.Formula, 2) & ",0)"</pre>
so if it ever needs to be rerun, you won't add it to one that has already been modified or ones that have different roundings.
Steve

20050613, 23:20 #4
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Inserting Certain Operations (2000)
Thanks, Hans. One last Q: what if I also have just plain data (numbers, not formulas)?

20050613, 23:35 #5
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Inserting Certain Operations (2000)
The line:
<pre> If oCell.HasFormula Then
</pre>
prevents the code from applying the rounding to any cell that does not contain a formula. What do you want it to do?Legare Coleman

20050613, 23:57 #6
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Inserting Certain Operations (2000)
Thanks, Steve.

20050614, 00:01 #7
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Inserting Certain Operations (2000)
Thanks, Legare. It was just an afterthought that I would like the solution to apply to any cell, whether it contains a formula or hard data.

20050614, 00:02 #8
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Inserting Certain Operations (2000)
Thanks, Hans.

20050614, 10:04 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Inserting Certain Operations (2000)
You could do something like this. It converts all number constants to a formula that rounds it (you could round the number and place the rounded value in the cell if desired) and then converts all number formulas to be the round function.
<pre>Option Explicit
Sub RoundAllNumbers()
Dim rSelect As Range
Dim rng As Range
Dim rCell As Range
Set rSelect = Selection
Set rng = Nothing
On Error Resume Next
Set rng = rSelect.SpecialCells(xlCellTypeConstants, xlNumbers)
On Error GoTo 0
If Not rng Is Nothing Then
For Each rCell In rng
rCell.Formula = "=ROUND(" & _
rCell.Value & ",0)"
Next
End If
Set rng = Nothing
On Error Resume Next
Set rng = rSelect.SpecialCells(xlCellTypeFormulas, xlNumbers)
On Error GoTo 0
If Not rng Is Nothing Then
For Each rCell In rng
If Left(rCell.Formula, 7) <> "=ROUND(" Then
rCell.Formula = "=ROUND(" & _
Mid(rCell.Formula, 2) & ",0)"
End If
Next
End If
Set rCell = Nothing
Set rng = Nothing
Set rSelect = Nothing
End Sub</pre>
Another option is to format the entire worksheet to the desired format (1, 2,3 cells or whatever) then go to tools  options  calculation (tab) and check "precision as displayed. This will (permanently) truncate the precision to the number of places each is set for.
Steve

20050614, 10:58 #10
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Inserting Certain Operations (2000)
Thanks, Steve, your code works like a charm.

20071120, 22:54 #11
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Inserting Certain Operations (2000)
Steve,
You very aptly helped me out a couple of years ago re this. I have since moved, and forgotten how to "run" this routine. I have inserted it into a module, but don't know how to run it.
HELP!!!

20071120, 23:00 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Inserting Certain Operations (2000)
Option 1:
 Select the cells you want to process.
 Press Alt+F8 or select Tools  Macro  Macros...
 Select RoundAllNumbers in the list of macros.
 Click Run.
Option 2:
 Select the cells you want to process.
 Press Alt+F11 to switch to the Visual Basic Editor.
 Click anywhere in the RoundAllNumbers macro code.
 Press F5 to run the macro.

20071120, 23:07 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Inserting Certain Operations (2000)
What is the name of the module in which you stored the macro?

20071120, 23:07 #14
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Inserting Certain Operations (2000)
Thanks Hans. While option 2 worked alright, no macro named "RoundAllNumbers" showed up in the macro window. Any ideas?

20071120, 23:12 #15
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Inserting Certain Operations (2000)
Module 3. Here is a sample.