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

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

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

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

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?

"Dave Peterson" wrote:

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

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)


"Wazooli" wrote in message
...
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?

"Dave Peterson" wrote:

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