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

Thus shows 15.0 % for me

Productivity = Format(Range("A1").Value, "#.0 %")


--
HTH

Bob Phillips

"rgarber50" <[email protected]> wrote
in message news:[email protected]...
>
> 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

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

Double-whammy Richard. Good to hear :-)

Bob

"rgarber50" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>