getting rid of trailing zeroes

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

Doesn't work. I guess what I need to do is extract each cell in the range
individually, and format according to whether there is a trailing zero or
not. The question is...

if rng = selection, how do i get individual cell.value information out?

 

[Bob Phillips]

Is this what you want?

For Each cell in rng
.Formula = "=" & cell.Address(0, 0) & "/max(" & cell.Address & ")"

myValue = .Value
If CLng(myValue) * 100 = myValue * 100 Then
myNumberFormat = "0%"
Else
myNumberFormat = "0.0%"
End If

cell.HorizontalAlignment = xlCenter
cell.NumberFormat = myNumberFormat
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)

Doesn't work. I guess what I need to do is extract each cell in the range
individually, and format according to whether there is a trailing zero or
not. The question is...

if rng = selection, how do i get individual cell.value information out?

 

[Bob Phillips]

The End With should be Next cell

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

dunno - will have to try tomorrow - thanks though

 

[Tom Ogilvy]

Sub Btn_click()
Dim rng As Range
Dim decide As Double
Dim sStr 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 & ")"
.HorizontalAlignment = xlCenter
.NumberFormat = "0.#%"
sStr = .Text
if right(sStr,1) = "0" or right(sStr,1) = "." then
.Numberformat = "0%"
end if
End With



End Sub

dunno - will have to try tomorrow - thanks though

 

[Dave Peterson]

I think that the sStr comparison should be something like:

If Right(sStr, 2) = ".%" Then
.NumberFormat = "0%"
End If

To the OP:

I'm not sure what this procedure is really doing. Are there multiples cells in
the current selection?

 

[Guest]

I finally got this to do what I want using the following code:

Sub Btn_click()
Dim rng As Range
Dim secondRng As Range
Dim myValue As Double
Dim myNumberFormat As String

Set rng = Selection
Set secondRng = Selection.Offset(0, 1)
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
For Each cell In secondRng
If (cell.Value * 100) - Int(cell.Value * 100) = 0 Then
cell.NumberFormat = "0%"
Else
cell.NumberFormat = "0.#%"
End If
Next
End With
End Sub


To answer Dave's last question...this is merely to simplify (!) my life. I
have to normalize parts of data sets every day (we generate alot of numbers
in science), and instead of having to constantly type and format the same
thing over and over, I figured VB would enable me to merely hit a button to
accomplish the same thing. To see what the above code does, see the
following:
71747.5625 75.8%
67155.30469 70.9%
63242.22266 66.8%
71437.63281 75.4%
76321.3125 80.6%
76078.48438 80.3%
74138.08594 78.3%
79189.05469 83.6%
76269.50781 80.6%
80015.89063 84.5%
84530.75 89.3%
78696.94531 83.1%
78329.67188 82.7%
87986.92188 92.9%
90748.36719 95.8%
94684.26563 100%
90675.4375 95.8%
90593.98438 95.7%
88713.875 93.7%
84550.17969 89.3%

As you can see, I simply need to normalize the first row to 100%. Values
that are integers have no trailing zeroes, whereas real numbers should have a
single decimal place for the significant figure. Much thanks and
appreciation to all who helped.

wazooli

 

[Dave Peterson]

Glad you got it working (and now I understand what you were doing!).

And don't forget to:

Dim Cell as Range