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

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

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

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

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

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