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
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: global trunc code or macro
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
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
CODE
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
=====================================
Eng-tips forums: The best place on the web for engineering discussions.