Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting rid of trailing zeroes
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting rid of trailing zeroes
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting rid of trailing zeroes
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting rid of trailing zeroes
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting rid of trailing zeroes
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting rid of trailing zeroes
The End With should be Next cell
-- HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting rid of trailing zeroes
dunno - will have to try tomorrow - thanks though
"Bob Phillips" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting rid of trailing zeroes
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 "Wazooli" wrote in message ... dunno - will have to try tomorrow - thanks though "Bob Phillips" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting rid of trailing zeroes
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? Tom Ogilvy wrote: 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 "Wazooli" wrote in message ... dunno - will have to try tomorrow - thanks though "Bob Phillips" wrote: 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 -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting rid of trailing zeroes
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" wrote: 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? Tom Ogilvy wrote: 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 "Wazooli" wrote in message ... dunno - will have to try tomorrow - thanks though "Bob Phillips" wrote: 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 -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting rid of trailing zeroes
Glad you got it working (and now I understand what you were doing!).
And don't forget to: Dim Cell as Range Wazooli wrote: 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" wrote: 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? Tom Ogilvy wrote: 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 "Wazooli" wrote in message ... dunno - will have to try tomorrow - thanks though "Bob Phillips" wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Include trailing zeroes in calculation | Excel Discussion (Misc queries) | |||
Can I set the default for "show zeroes" to not show the zeroes? | Setting up and Configuration of Excel | |||
eliminating trailing zeroes imported into word document from excel file | Excel Discussion (Misc queries) | |||
Sorry, leading zeroes again :-(( | Excel Discussion (Misc queries) | |||
excel .txt to .cvs, lose trailing zeroes in numeric field | Excel Discussion (Misc queries) |