getting rid of trailing zeroes
Maybe...
Option Explicit
Sub Btn_click()
Dim rng As Range
Dim decide As Double
Dim myValue As Double
Dim myNumberFormat As String
Set rng = Selection
If rng.Areas.Count 1 Then Exit Sub
If rng.Columns.Count 1 Then Exit Sub
If Application.Count(rng) = 0 Or Application.Max(rng) = 0 Then
MsgBox "No numbers"
Exit Sub
End If
With rng.Offset(0, 1)
.Formula = "=" & rng(1).Address(0, 0) & "/max(" & rng.Address & ")"
myValue = .Value
If CLng(myValue) * 100 = myValue * 100 Then
myNumberFormat = "0%"
Else
myNumberFormat = "0.#%"
End If
.HorizontalAlignment = xlCenter
.NumberFormat = myNumberFormat
End With
End Sub
Wazooli wrote:
I need to fit it into the existing code I have:
Sub Btn_click()
Dim rng As Range
Dim decide As Double
Set rng = Selection
If rng.Areas.count 1 Then Exit Sub
If rng.Columns.count 1 Then Exit Sub
If Application.count(rng) = 0 Or Application.Max(rng) = 0 Then
MsgBox "No numbers"
Exit Sub
End If
With rng.Offset(0, 1)
.Formula = "=" & rng(1).Address(0, 0) & "/max(" & rng.Address & ")"
.HorizontalAlignment = xlCenter
.NumberFormat = "0.#%"
End With
End Sub
(code supplied kindly by Tom O., modified slightly by me)
wazooli
"Dave Peterson" wrote:
Dim myValue as double
dim myNumberFormat as string
myValue = 1.000
if clng(myvalue) * 100 = myvalue * 100 then
mynumberformat = "##0"
else
mynumberformat = "##0.0"
end if
Wazooli wrote:
How can I use the numberformat property as such:
1.000 becomes 100%
0.547 becomes 54.7%
i currently have .numberformat = "0.#%", which gives
1.000 - 100.% (dangling decimal point)
0.547 - 54.7% (correct)
.numberformat = "0.0%" gives
1.000 - 100.0% (dangling zero)
0.547 - 54.7% (correct)
wazooli
--
Dave Peterson
--
Dave Peterson
|