×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

global trunc code or macro

global trunc code or macro

global trunc code or macro

(OP)
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?

RE: global trunc code or macro

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.

CODE

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)

RE: global trunc code or macro

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

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources