Home |
Search |
Today's Posts |
#1
|
|||
|
|||
display cell value in msgbox formatted as %
Hi I have been trying to come up with a way to display a cell value in a msgbox so that it formats properly as a percent. I have tried: Productivity = Format(Range("A1").Value, "###,# %") Msgbox Productivity This always gives me a leading 0 (e.g 015%) and I want it to display 15.0%. So I tried this: Productivity = Format(CStr(Range("A1").Value) * 100, "#,###.0") & "%" and it works ok but... I want to use the value of productivity in computations - which I can't formatted as a string ... Am I missing something - or is it as simple as declaring a separate numeric variable for productivity? Thanks Richard -- rgarber50 ------------------------------------------------------------------------ rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350 View this thread: http://www.excelforum.com/showthread...hreadid=385873 |
#2
|
|||
|
|||
Thus shows 15.0 % for me
Productivity = Format(Range("A1").Value, "#.0 %") -- HTH Bob Phillips "rgarber50" wrote in message ... Hi I have been trying to come up with a way to display a cell value in a msgbox so that it formats properly as a percent. I have tried: Productivity = Format(Range("A1").Value, "###,# %") Msgbox Productivity This always gives me a leading 0 (e.g 015%) and I want it to display 15.0%. So I tried this: Productivity = Format(CStr(Range("A1").Value) * 100, "#,###.0") & "%" and it works ok but... I want to use the value of productivity in computations - which I can't formatted as a string ... Am I missing something - or is it as simple as declaring a separate numeric variable for productivity? Thanks Richard -- rgarber50 ------------------------------------------------------------------------ rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350 View this thread: http://www.excelforum.com/showthread...hreadid=385873 |
#3
|
|||
|
|||
When I try: Productivity = Format(Range("A1").Value, "#.0 %") I get 070.0%. Now I am using a Macintosh - wonder if that is effecting the formatting? Here is a test macro I tried - it also errors out at the do while loop. [A1 is a % formatted formula =B3; B3 is a formula B5/B4; B4 =40 and B5 = 20). The idea here is how much does B5 (key) have to be for productivity(A1) to equal 70%. Of course the answer in the test is 28 (70% productivity in a 40/hr week = 28hrs) Public Sub Testvalues() Dim Productivity, Key Productivity = Format(Range("A1").Value, "#.0 %") MsgBox Productivity ' returns 070.0% Key = Range("A5").Value Do While Productivity < 0.7 '- runtime error 13; type mismatch Key.Value = Key + 0.25 Loop End Sub Any ideas would be appreciated. Thanks Richard -- rgarber50 ------------------------------------------------------------------------ rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350 View this thread: http://www.excelforum.com/showthread...hreadid=385873 |
#4
|
|||
|
|||
When you do this:
Productivity = Format(Range("A1").Value, "#.0 %") It makes Productivity a string--not a number. If you want to display that percentage and the cell is formatted the way you want, you could use: msgbox range("a1").text But if you're going to use productivity as a number later, it's best to just work with the value (why convert it to text just to convert it back to a number?). And sometimes you use Key as a range and sometimes you use it as a value. This kind of thing works ok for me: Option Explicit Public Sub Testvalues() Dim Productivity As Double Dim Key As Double Productivity = Range("A1").Value MsgBox Range("a1").Text Key = Range("A5").Value Do While Productivity < 0.7 Key = Key + 0.25 'you better do something to productivity so you can exit the loop Productivity = Productivity + 0.1 '??? Loop Range("a5").Value = Key End Sub rgarber50 wrote: When I try: Productivity = Format(Range("A1").Value, "#.0 %") I get 070.0%. Now I am using a Macintosh - wonder if that is effecting the formatting? Here is a test macro I tried - it also errors out at the do while loop. [A1 is a % formatted formula =B3; B3 is a formula B5/B4; B4 =40 and B5 = 20). The idea here is how much does B5 (key) have to be for productivity(A1) to equal 70%. Of course the answer in the test is 28 (70% productivity in a 40/hr week = 28hrs) Public Sub Testvalues() Dim Productivity, Key Productivity = Format(Range("A1").Value, "#.0 %") MsgBox Productivity ' returns 070.0% Key = Range("A5").Value Do While Productivity < 0.7 '- runtime error 13; type mismatch Key.Value = Key + 0.25 Loop End Sub Any ideas would be appreciated. Thanks Richard -- rgarber50 ------------------------------------------------------------------------ rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350 View this thread: http://www.excelforum.com/showthread...hreadid=385873 -- Dave Peterson |
#5
|
|||
|
|||
Dave & Bob Thanks so much! I am (obviously) a beginner with VBA - when I played around with your explanations it not only solved the problem I was working on - it also really helped me get some basic concepts. Thanks again. Richard -- rgarber50 ------------------------------------------------------------------------ rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350 View this thread: http://www.excelforum.com/showthread...hreadid=385873 |
#6
|
|||
|
|||
Double-whammy Richard. Good to hear :-)
Bob "rgarber50" wrote in message ... Dave & Bob Thanks so much! I am (obviously) a beginner with VBA - when I played around with your explanations it not only solved the problem I was working on - it also really helped me get some basic concepts. Thanks again. Richard -- rgarber50 ------------------------------------------------------------------------ rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350 View this thread: http://www.excelforum.com/showthread...hreadid=385873 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
number formatted cell | Excel Discussion (Misc queries) | |||
Need Workaround for Cell Display Limitation in Excel 2000 | Excel Discussion (Misc queries) | |||
up to 7 functions? | Excel Worksheet Functions | |||
display values in cell | Excel Discussion (Misc queries) | |||
Display actual contents of cell | Excel Discussion (Misc queries) |