Issues with 3 sig figs VBA code

[mdeavila]

Thank you for your help in advance!

I have this macro to update significant figures that has been working great for some time, but recently we unlocked a spreadsheet to copy/paste data into a new program and we noticed that not all of the values are being actually truncated to the 3 sig figs we require. We were seeing this with values less than 1000 but I was able to correct this to values down to 100 and now I am stuck not knowing where else to look.

Any values less than 99.9 won't display correctly, I am attaching a spreadsheet as an example.

The weird part is that 3 sig figs are actually displayed, but when you click on the cell, the numbers are not actually updated to 3 sig figs (numbers below 100 that is). Nmbers highlited in yellow are basically incorrect, although display is correct, I hope this makes sense.

This is the code that has been 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"



'Negative numbers

Case Is <= -1000

Selection.NumberFormat = "0"

Case Is <= -100

Selection.NumberFormat = "0"

Case Is <= -10

Selection.NumberFormat = "0.0"

Case Is <= -1

Selection.NumberFormat = "0.00"

Case Is <= -0.1

Selection.NumberFormat = "0.000"

Case Is <= -0.01

Selection.NumberFormat = "0.0000"

Case Is <= -0.001

Selection.NumberFormat = "0.00000"

Case Is <= -0.0001

Selection.NumberFormat = "0.000000"

Case Is <= -0.00001

Selection.NumberFormat = "0.0000000"

Case Is <= -0.000001

Selection.NumberFormat = "0.00000000"

Case Is <= -0.0000001

Selection.NumberFormat = "0.000000000"

End Select

Else: Selection.NumberFormat = "@"

End If



End Sub









Sub Round_Calc()



Dim sglOrigValue As Single

Dim sglDigits As Single

Dim sglRoundedValue As Single



If IsNumeric(ActiveCell) Then

Select Case ActiveCell

Case Is = "0"

Selection.NumberFormat = "0.00"

Case Is >= 1000

sglOrigValue = ActiveCell.Value

Range("IV1").Formula = "=INT(LOG(" & sglOrigValue & ")-2)"

sglDigits = Range("IV1").Value

Range("IV2") = "=ROUND(" & sglOrigValue & ",-" & sglDigits & ")"

sglRoundedValue = Range("IV2").Value

ActiveCell = sglRoundedValue

Case Is <= 1000

sglOrigValue = ActiveCell.Value

Range("IV1").Formula = "=INT(LOG(" & sglOrigValue & ")-2)"

sglDigits = Range("IV1").Value

Range("IV2") = "=ROUND(" & sglOrigValue & ",-" & sglDigits & ")"

sglRoundedValue = Range("IV2").Value

ActiveCell = sglRoundedValue

Case Is <= 100

sglOrigValue = ActiveCell.Value

Range("IV1").Formula = "=INT(LOG(" & sglOrigValue & ")-1)"

sglDigits = Range("IV1").Value

Range("IV2") = "=ROUND(" & sglOrigValue & ",-" & sglDigits & ")"

sglRoundedValue = Range("IV2").Value

ActiveCell = sglRoundedValue







End Select

End If

Range("IV1:IV2").ClearContents



End Sub

I basically added this portion and it seemed to have solved the issue with numbers less than 1000, but when adding the very last part (for numbers below 100) it does not seem like it made any differences

Case Is <= 1000

sglOrigValue = ActiveCell.Value

Range("IV1").Formula = "=INT(LOG(" & sglOrigValue & ")-2)"

sglDigits = Range("IV1").Value

Range("IV2") = "=ROUND(" & sglOrigValue & ",-" & sglDigits & ")"

sglRoundedValue = Range("IV2").Value

ActiveCell = sglRoundedValue

 

[StephenCrump]

Welcome to the Forum!

Try changing your variables declared as Single to Double.

 

[Sulprobil]

Hello,
Can't you just wrap your numbers with

CDbl(Format(x,"0.00E000"))

or even without VBA as worksheet function

=--TEXT(x,"0.00E000"))

?
Regards,
Bernd

 

[mdeavila]

Welcome to the Forum!

Try changing your variables declared as Single to Double.

do you suggest I change to Double in all instances?

 

[mdeavila]

Hello,
Can't you just wrap your numbers with

CDbl(Format(x,"0.00E000"))

or even without VBA as worksheet function

=--TEXT(x,"0.00E000"))

?
Regards,
Bernd

The issues with something like this is that the data that I need to update varies a lot, the macro above works, except when copying/pasting data into a different program outside of excel. Somehow the display is correct, but once I click on the cell the displayed value is different.