Posts by mdeavila

Hi, I am having the same exact issue as the OP. I figured out a code to spit out the correct number of sig figs in every cell when running the macro, my issue is with numbers below 100, where the rounding is effective but the display numbers are not rounded to 3 sig figs effectively.

Curious if anyone has figured out a solution to this issue?

Quote from royUK

what are these Procedures?

Format_Sig_Figs

Round_Calc

do the cells contain Text or Formulas?

Which column are you using?

It might be easier an example of your workbook.

Display More

These pretty much dictate the significant figure and rounding calculations to be used:

Sub Format_Sig_Figs()


ActiveCell.Select
If IsNumeric(ActiveCell) Then
Select Case ActiveCell
Case Is = 0
Selection.NumberFormat = "0.00"
Case Is >= 99.95
Selection.NumberFormat = "0"
Case Is >= 9.995
Selection.NumberFormat = "0.0"
Case Is >= 0.9995
Selection.NumberFormat = "0.00"
Case Is >= 0.09995
Selection.NumberFormat = "0.000"
Case Is >= 0.009995
Selection.NumberFormat = "0.0000"
Case Is >= 0.0009995
Selection.NumberFormat = "0.00000"
Case Is >= 0.00009995
Selection.NumberFormat = "0.000000"
Case Is >= 0.000009995
Selection.NumberFormat = "0.0000000"
Case Is >= 0.0000009995
Selection.NumberFormat = "0.00000000"
Case Is >= 0.00000009995
Selection.NumberFormat = "0.000000000"


End Select
Else: Selection.NumberFormat = "@"
End If


End Sub

Quote from Justin Doward

Hi MD,

Your code literally says to stop when you get to a blank cell, just redefine the end point somehow. Eg do until activecell.row = 1000 or wherever your last table row is likely to be.

I literally copied this code from another sheet that had a similar code to what I needed to apply. if I redefine the activecell - would the code be changed to this?

Do Until ActiveCell.Row = "1000"

I'm literally a novice to this. I appreciate everyone's help!

How would I best apply a code to allow the macro to modify my data without stopping in areas where there is a space?

The code above has worked very well, except in instances where there is an empty cell, it does not continue to the next cell without selecting the areas again. This is rather inconvenient as I often have data sets separated by an empty cell.

thanks in advance

I have large sets of data that are generated from a system and without a macro it is hard to ensure all of the data is edited to 3 significat digits without missing a few points. I hope this helps.

I created a macro to round values in a spreadsheet to 3 sig digits, but I am struggling in getting the code to format values that are past an empty cell - it stops right at the empty cell, but I want this macro to be applied to all the values on a sheet and move to the next cell after an empty cell.

I could only figure out how to apply it to rows and columns so I created two pieces to it (which isn't a big deal for me).

I don't have a lot of experience here, but any help is much appreciated!

Here is my code:

Do Until ActiveCell.Value = ""
ActiveCell.Select
Format_Sig_Figs
Round_Calc
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Thanks in advance!

Moderator Note: I have added code tags for you. Please read the forum rules you agreed to abide by.