问题

I need to show two significant digits, but it is important that the original data does not get lost.

Currently I have a macro that uses ROUND, however this changes the actual number instead of changing only the decimal places.

cell.Value = Round(cell, 1 - (1 + Int(Log(Abs(cell)) / Log(10))))

For other numbers I have

cell.NumberFormat = "#"

Then Excel shows only how many numbers I tell it to but I cannot get a variable number of digits, right?

Is there a way to combine these to so that excel will show two significant numbers?

cell.Value = Round(cell, 1 - (1 + Int(Log(Abs(cell)) / Log(10))))

cell.NumberFormat = "#"

答案1

得分: 3

以下是您要翻译的代码部分：

What you can do is you can calculate how many digits you want to show as you did:

    Dim Digits As Long
    Digits = 1 - (1 + Int(Log(Abs(Cell.Value)) / Log(10)))

And then set the number format accordingly

    If Digits > 0 Then
        Cell.NumberFormat = "0." & String(Digits, "0")
    Else
        Cell.NumberFormat = "#"
    End If

So if `Digits` is `1` it will use `0.0` as number format and if it is `2` it will use `0.00` as number format.

So for example the following numbers will show as below

[![enter image description here][1]][1]

> **Note**  
> Don't use this solution if there are formulas in those cells you try to format. Because if the value changes because the result of the formula changes, then the number format will **not change accordingly** and stay the old one.  
> Note that every cell will have set it's own number format.

请注意，这是代码的翻译部分，没有其他内容。

Dim Digits As Long
Digits = 1 - (1 + Int(Log(Abs(Cell.Value)) / Log(10)))

If Digits > 0 Then
    Cell.NumberFormat = "0." & String(Digits, "0")
Else
    Cell.NumberFormat = "#"
End If