Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / May 2006

Tip: Looking for answers? Try searching our database.

Rounding-Formulas

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
fak119 - 10 May 2006 11:41 GMT
Is there (or can there be) a macro or a function that changes a formula like

=C1*$R4   in  =round(C1*$R4;0)  etc.

I have many spreadsheets with hundreds of formulas on each sheet that need
to be rounded. I would have to change individually every single formula. Is
there an easier way?
Niek Otten - 10 May 2006 12:37 GMT
Depending on how you formatted the cells, you might consider

Tools>Options>Calculation tab, check Precision as displayed. Look in HELP to read the consequences.

Signature

Kind regards,

Niek Otten

| Is there (or can there be) a macro or a function that changes a formula like
|
[quoted text clipped - 3 lines]
| to be rounded. I would have to change individually every single formula. Is
| there an easier way?
JE McGimpsey - 10 May 2006 13:15 GMT
One way:

   Public Sub WrapARound()
       Const sWRAPPER As String = "=Round(#; 0)"
       Dim ws As Worksheet
       Dim rFormulae As Range
       Dim rCell As Range
       For Each ws In ActiveWorkbook.Worksheets
           On Error Resume Next 'in case no formulae
           Set rFormulae = ws.Cells.SpecialCells(xlCellTypeFormulas)
           On Error GoTo 0
           If Not rFormulae Is Nothing Then
               For Each rCell In rFormulae
                   With rCell
                       If Not .Formula Like "=ROUND(*" Then _
                           .Formula = Replace( _
                                       sWRAPPER, "#", Mid(.Formula, 2))
                   End With
               Next rCell
           End If
           Set rFormulae = Nothing
       Next ws
   End Sub

> Is there (or can there be) a macro or a function that changes a formula like
>
[quoted text clipped - 3 lines]
> to be rounded. I would have to change individually every single formula. Is
> there an easier way?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.