Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 9th 05, 10:20 PM
rgarber50
 
Posts: n/a
Default 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   Report Post  
Old July 9th 05, 11:29 PM
Bob Phillips
 
Posts: n/a
Default

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   Report Post  
Old July 10th 05, 03:56 AM
rgarber50
 
Posts: n/a
Default


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   Report Post  
Old July 10th 05, 11:47 AM
Dave Peterson
 
Posts: n/a
Default

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   Report Post  
Old July 10th 05, 02:59 PM
rgarber50
 
Posts: n/a
Default


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   Report Post  
Old July 10th 05, 03:27 PM
Bob Phillips
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
number formatted cell Pam Coleman Excel Discussion (Misc queries) 2 June 2nd 05 08:44 PM
Need Workaround for Cell Display Limitation in Excel 2000 Kevin Excel Discussion (Misc queries) 5 April 20th 05 11:33 PM
up to 7 functions? ALex Excel Worksheet Functions 10 April 12th 05 06:42 PM
display values in cell Vincent Excel Discussion (Misc queries) 2 February 21st 05 04:16 PM
Display actual contents of cell xmasbob Excel Discussion (Misc queries) 1 December 6th 04 05:09 PM


All times are GMT +1. The time now is 12:21 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017