Sig Figs and Other Rules (Excel 2003)

Topic

New Reply

Hi,

This significant figures and displaying ending zeros has been irritating me for over 2 weeks … I have used various posts from this site to help, but I am stuck!
This company has these rules for displaying a number:
–If # less than 1, use 3 decimal points–rounded but display ending 0s
–If 1 <= # = 1000, use rounded whole number
–Must be a number (not text) so that it can be averaged and graphed

Using IF and FIXED functions, I get close …

In the attached spreadsheet, I use functions. However, I can use VBA and have the users click a button after they enter the data that will generate the number.

As always, any help/direction is appreciated!

Thanks,
Cindy

Reply | Quote

Replies

You could use the Worksheet Change event VBA routine to round the value and set the display format depending on the range the number entered was in.

Reply | Quote

Thanks, Legare.

This is the macro I came up with. I had to fudge the ranges, due to rounding, but I think it works.
I’m not sure where to place it for a Worksheet Change event, though. I’m sorry.

We have a Quality Control department that takes the displayed numbers in reports and uses a calculator to determine the accuracy of the numbers.
Since the cell still contains the original number, I will need to use the “Precision Displayed” setting in Options for any further calculation.
And linking these cells to other locations, means that I will need to run a macro in the other locations, too, if I want the same type of display. The first time the link is done, it picks up the numberformat of the cell. However, if the data/cell changes, the linked cell will still contain the original numberfor. Hmmm… I think I’m getting a headache!

Anyways, how do I do a Worksheet Change Event?

Thanks,
–cat

Sub DecimalDisplay()
‘
For r = 9 To 35
CalcNum = Range(“C” & r).Value
If CalcNum = 0.9995 And CalcNum = 9.995 And CalcNum = 99.95 Then
Range(“D” & r).NumberFormat = “0”
Range(“D” & r).Value = CalcNum
End If

Next r

End Sub

Reply | Quote

If you right-click the worksheet tab and select View Code, you’ll see the code module for the worksheet. This is where you create event procedures such as Worksheet_Change.

See the attached version of your workbook.

Reply | Quote

If I understand your description and workbook correctly, you could use this procedure:

Sub FormatCells(rng As Range)
Dim rngCell As Range
For Each rngCell In rng.Cells
If Not rngCell = “” Then
If IsNumeric(rngCell) Then
Select Case Abs(rngCell)
Case Is < 0.995
rngCell.NumberFormat = "0.000"
Case Is < 9.95
rngCell.NumberFormat = "0.00"
Case Is < 99.5
rngCell.NumberFormat = "0.0"
Case Else
rngCell.NumberFormat = "0"
End Select
End If
End If
Next rngCell
End Sub

To apply it to a range, use a macro like this:

Sub FormatRange()
FormatCells Range("C9:C35")
End Sub

To format cells as they are entered, you can use the Worksheet_Change event, as suggested by Legare (code goes into the worksheet module)

Private Sub Worksheet_Change(ByVal Target As Range)
FormatCells Target
End Sub

or if you want to restrict it to a specific range:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C9:C35")) Is Nothing Then
FormatCells Intersect(Target, Range("C9:C35"))
End If
End Sub

If you also want to format the result of formulas as their arguments change, you'd need to use the Worksheet_Calculate event. But that could cause quite a bit of overhead.

Reply | Quote

Hi Hans,

I was creating my reply to Legare I guess while you were posting your reply ! You have answered most of my questions … is Target the name of the worksheet?
Also, how do you keep the indenting in your replies?

Thanks,
–cat

Reply | Quote

No, Target is not the name of the worksheet. Target is a parameter of type Range object that is passed to the Worksheet Change event routine. Target is set to the cell or cells that were changed on the worksheet and caused the change event to fire.

You keep the formatting in code by using [ code] and [ tab] tags around the code. Hans’ code would look like this when he posted it (without the spaces after the left brakets):

[ code]
Sub FormatCells(rng As Range)
[ tab]Dim rngCell As Range
[ tab]For Each rngCell In rng.Cells
[ tab][ tab]If Not rngCell = “” Then
[ tab][ tab][ tab]If IsNumeric(rngCell) Then
[ tab][ tab][ tab][ tab]Select Case Abs(rngCell)
[ tab][ tab][ tab][ tab][ tab]Case Is < 0.995
[ tab][ tab][ tab][ tab][ tab][ tab]rngCell.NumberFormat = "0.000"
[ tab][ tab][ tab][ tab][ tab]Case Is < 9.95
[ tab][ tab][ tab][ tab][ tab][ tab]rngCell.NumberFormat = "0.00"
[ tab][ tab][ tab][ tab][ tab]Case Is < 99.5
[ tab][ tab][ tab][ tab][ tab][ tab]rngCell.NumberFormat = "0.0"
[ tab][ tab][ tab][ tab][ tab]Case Else
[ tab][ tab][ tab][ tab][ tab][ tab]rngCell.NumberFormat = "0"
[ tab][ tab][ tab][ tab]End Select
[ tab][ tab][ tab]End If
[ tab][ tab]End If
[ tab]Next rngCell
End Sub
[ /code]

I use the two Word macros below, adapted from macros that Hans posted, to format code before pasting it into posts.


Sub HLCSpaces2Tabs()
On Error GoTo Err_Sub
Selection.Find.Execute FindText:=" ", ReplaceWith:=" ", Replace:=wdReplaceAll
Exit Sub
Err_Sub:
If Err 91 Then
MsgBox Err.Description, vbExclamation
End If
End Sub

Sub HLCFormatCode()
Selection.WholeStory
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.Paste
Selection.WholeStory
Application.Run MacroName:="HLCSpaces2Tabs"
Selection.HomeKey Unit:=wdLine
Selection.TypeText Text:="

Code:

"
	Selection.TypeParagraph
	Selection.EndKey Unit:=wdStory
	Selection.TypeParagraph
	Selection.TypeText Text:="

"
Selection.TypeParagraph
Selection.WholeStory
Selection.Copy
End Sub

Reply | Quote