+ Reply to Thread
Results 1 to 6 of 6

display cell value in msgbox formatted as %

  1. #1
    Registered User
    Join Date
    07-04-2004
    Posts
    33

    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

  2. #2
    Bob Phillips
    Guest

    Re: display cell value in msgbox formatted as %

    Thus shows 15.0 % for me

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


    --
    HTH

    Bob Phillips

    "rgarber50" <rgarber50.1rxaea_1120946701.8866@excelforum-nospam.com> wrote
    in message news:rgarber50.1rxaea_1120946701.8866@excelforum-nospam.com...
    >
    > 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. #3
    Registered User
    Join Date
    07-04-2004
    Posts
    33

    display cell value in msgbox formatted as %

    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

  4. #4
    Dave Peterson
    Guest

    Re: display cell value in msgbox formatted as %

    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. #5
    Registered User
    Join Date
    07-04-2004
    Posts
    33

    Re: display cell value in msgbox formatted as %

    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
    Last edited by rgarber50; 07-10-2005 at 10:00 AM. Reason: caught mistake in post

  6. #6
    Bob Phillips
    Guest

    Re: display cell value in msgbox formatted as %

    Double-whammy Richard. Good to hear :-)

    Bob

    "rgarber50" <rgarber50.1ryiua_1121004302.4191@excelforum-nospam.com> wrote
    in message news:rgarber50.1ryiua_1121004302.4191@excelforum-nospam.com...
    >
    > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1