global trunc code or macro

[youngEIT]

I have a spreadsheet in which i need to TRUNC all numbers (inputs and formula results) to 2 digits. does anyone have a macro that can do that globally. the spreadsheet i am talking about has about 12 tabs and a large amount of data; doing one at a time would be brutal.also, is there a way to put a code in future spreadsheets so that once an input is entered, it will automatically trunc it to the first 2 digits?

 

[IRstuff]

Can't you use the Precision as Displayed setting in Options?

TTFN

FAQ731-376

 

[handleman]

This macro will add a TRUNC function to every cell in the entire worksheet that contains numeric data (either number or formula result) and is not already TRUNC'ed. All original data/input is preserved.

Do you really want TRUNC (discard all decimal data past 2) or ROUND?

Note that data in the spreadsheet will actually change since any formula that references another cell will now use the truncated value of the cell rather than the original value.

Sub TruncAll()
Dim mySht As Excel.Worksheet
Dim myCell As Range
Dim tmpString As String


For Each mySht In Worksheets
    For Each myCell In mySht.UsedRange.Cells
        If (Not myCell.Formula = "") And (Not Left(myCell.Formula, 6) = "=TRUNC") And IsNumeric(myCell.Text) Then
            If Left(myCell.Formula, 1) = "=" Then
                tmpString = "=TRUNC(" & _
                Right(myCell.Formula, Len(myCell.Formula) - 1) & _
                ",2)"
            Else
                tmpString = "=TRUNC(" & myCell.Formula & ",2)"
            End If
            myCell.Formula = tmpString
        End If
    Next
Next
End Sub

-handleman, CSWP (The new, easy test)

 

[electricpete]

A small modification of the above code changes the numeric formatting (*) of all numeric items to scientific notation with two significant figures:

Sub Formatx2All()
Dim mySht As Excel.Worksheet
Dim myCell As Range
Dim tmpString As String

For Each mySht In Worksheets
    For Each myCell In mySht.UsedRange.Cells
        If (Not myCell.Formula = "") And IsNumeric(myCell.Text) Then
            myCell.NumberFormat = "0.0E+00"
        End If
    Next
Next
End Sub

* Note - this affects display and printing only, not the stored values.


=====================================
Eng-tips forums: The best place on the web for engineering discussions.