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?action=getinfo&userid=11350
View this thread: http://www.excelforum.com/showthread.php?threadid=385873

0
7/9/2005 9:20:08 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
514 Views

Similar Articles

[PageSpeed] 16

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?action=getinfo&userid=11350
> View this thread: http://www.excelforum.com/showthread.php?threadid=385873
>


0
phillips1 (803)
7/9/2005 10:29:29 PM
When I try:
Productivity = Format(Range("A1").Value, "#.0 %")

I get 070.0%. Now I am using a Macintosh - wonder if that is effectin
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 B
= 20). The idea here is how much does B5 (key) have to be fo
productivity(A1) to equal 70%.  Of course the answer in the test is 2
(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; typ
mismatch 
Key.Value = Key + 0.25
Loop


End Sub

Any ideas would be appreciated.
Thanks
Richar

--
rgarber5
-----------------------------------------------------------------------
rgarber50's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1135
View this thread: http://www.excelforum.com/showthread.php?threadid=38587

0
7/10/2005 2:56:32 AM
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?action=getinfo&userid=11350
> View this thread: http://www.excelforum.com/showthread.php?threadid=385873

-- 

Dave Peterson
0
petersod (12004)
7/10/2005 10:47:09 AM
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?action=getinfo&userid=11350
View this thread: http://www.excelforum.com/showthread.php?threadid=385873

0
7/10/2005 1:59:15 PM
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?action=getinfo&userid=11350
> View this thread: http://www.excelforum.com/showthread.php?threadid=385873
>


0
phillips1 (803)
7/10/2005 2:27:25 PM
Reply:

Similar Artilces:

macro to move cursor one cell right
If I need to move the cursor to the right to paste a value copied from another sheet , what macro command should I use You don't need to. The Copy Method accepts a Range argument which would be the destination for the paste operation. For example: Worksheets(1).Range("A1").Copy Destination:=3DWorksheets(3).Range("B12") --JP On Sep 22, 3:31=A0pm, Kodak1993 <Kodak1...@discussions.microsoft.com> wrote: > If I need to move the cursor to the right to paste a value copied from > another sheet , what macro command should I use If JP's reply does not do...

how to copy the same cell across different work books into another workbook easily?
i have the daily sales from 1.xls, 2.xls likewise till 31.xls, in a single folder. now i have a final consolidated workbook called final.xls, wherein i would want to copy the same cell across all the workbooks into final.xls(which is again in the same folder) easily,..someway like the fill handle or is there someother way, other than selecting each time the cell to be linked??? can anyone help, this is really breaking my head,,.... -- sageerai ------------------------------------------------------------------------ sageerai's Profile: http://www.excelforum.com/member.php?action=getinfo...

Format for elapsed time
I have data from my 4th grade class lab on the amount of time to melt an ice cube, i.e., elapsed time, but the only way Excel 2007 seems to interpret time values (either in the Time or Custom categories for formatting data) are as time of day, even mm/ss shows as such. How can I show 4 minutes and 35 seconds as just that, not 4:35 AM?? Formatting controls what the cell displays, not what's in the formula bar. Just use a format of: h:mm -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Mr. B in VT" wrote: > I have ...

Exchange 2k3
I have a real quick question regarding Internet Message Formats and Exchange 2k3. Basically, I'd like all exchange users to email using the default Internet Message. The domain is * for this rule. However, whenever users email the domain: x.domain.org - (a local domain btw, it's a unix machine that accepts smtp) - i would like uuencode, plain text used only. However, my problem is that the new domain I created in Internet Message Formats doesn't work. I created ad added the domain - x.domain.org - and set the message format to uuencode and never use "rich-text format&...

Change a cell's fill color dynamically?
Is it possible to lookup a value in a cell over here and change a cell's fill color over there based on certain criteria? For instance, if all the workdays for a month are listed in column A, is it possible to look up all the Fridays and change the corresponding cell in Column C from whatever color to Yellow? While I'm at it, is it possible to unlock those certain C cells for editing, as well? Thank You so very much. Arlen You can handle the color issue with conditional formatting; you don't need any lookup function. As far as locking/unlocking the cells, you probably ...

Credit and Debit formating
I have workbook that has one tab that pending credits and charges are entered by associates who are not Excel suavy. That information is linked to a cell for reconcilation. The purpose is to figure a inventory. How it works is actual count plus pending items to come up with a new inventory balance. The problem is I need to add back into the inventory the credits and take out the charges. Do to the fact that these credit debits are entered in the same place. What I am trying to do is convert the credit to charges and subtract the debit (opposite of value). Is there a formula that can do ...

Displaying Map data
Have created a map from data in Excel file, but need to display data from (4) columns, and there are only (3) display formats available - category shading - dot density - graduated symbol Is there a solution I'm not aware of? All help appreciated! Sandi ...

How can I check a cell for current date and insert it if blank?
I am modifying the invoice template. I want to create a formaula that checks the date cell for the current date. If a date occupies the cell nothing happens, otherwise, the current date is inserted. Can this be done? Thanks Don Sub insertdate() With ActiveCell If Not IsDate(.Value) Then .Value = Date End With -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Don K" <Don K@discussions.microsoft.com> wrote in message news:85927992-1BA5-4B6A-94A7-AFFCCB607BD7@microsoft.com... >I am modifying the invoice template. I want to create a formaula that >checks >...

Protecting cell contents
I have about 5 cells on my sheet that contain formulas which reference other sheets. I therefore want to prevent users from accidentally changing or deleting these 5 formulas. They are free to modify any other data on the sheet. How can I preserve these 5 cells which cointain formulas without using the "Protect Sheet" options??? BTW, users have the ability to protect and unprotect this sheet at any time with their own passwords, so it seems like I need another way to protect my formulas. I mean, once the user un-protects the sheet, they are able to delete anything an...

VLook-Up Possibly Conditional Formatting
I am working on a spreadsheet that has three columns. A(Equipment Description) C (Part Number) and AA (Price Each) What I would like to be able to do is to choose either column A as a drop down or Column C as a drop down (be able to start entering and it jump to the description) and after I choose an item out of A or C that it would auto populate the the other colmun that was not choosen along with the price. The other catch is that I do not want the price list description nor part number within the same workbook. Thank you in advance for your assistance. You can create dr...

Form mysteriously stops accepting VB changes to values in text boxes????
Hello Everyone, Something mysterious has happened to my form: I had code to the following effect being executed as part of Form_Open: With Me ... ... .txtDIC.Value = intEDays ... ... End With Where .txtDIC is a bound text box control. Everything was fine until recently, when I started getting the error message: **************** Run-time error '2448' You can't assign a value to this object **************** I replaced it with... strSQL = "UPDATE tblCustOrds SET DaysInCell = " & intEDays & " WHERE ID = " & ..OpenArgs CurrentDb()....

Cell Styles on Ribbon
The context window that should "pop-up/out" when I click on Cell Styles on the Home tab is "locked" on to my ribbon. Normal, Bad, Good, etc. is on one line and Check Cell, Explanatory, Input, etc is on the second row. I looks like something that you would do if you wanted to modify a ribbon. What is your question? -- HTH Bob "Stephen J" <Stephen J@discussions.microsoft.com> wrote in message news:D1F8E067-FD46-442F-81BF-9F28AFE465BE@microsoft.com... > The context window that should "pop-up/out" when I click on Cell Style...

IF clause for a range of cells
The following formula works well for me but is there a way to make it shorter by defining a range of cells, rather than individual cells, in the IF part? =SUM(G8:AJ8)+IF(G8="",4)+IF(H8="",4)+IF(I8="",4)+IF(J8="",4)+IF(K8="",4)+IF(L8="",4)+IF(M8="",4)+IF(N8="",4)+IF(O8="",4)+IF(P8="",4)+IF(Q8="",4)+IF(R8="",4)+IF(S8="",4)+IF(T8="",4)+IF(U8="",4)+IF(V8="",4)+IF(W8="",4)+IF(X8="",4)+IF(Y8="",4)+IF(Z8=&quo...

Selection List box in a cell?
How do I use one sheet to allow the user to enter text in each cell of a column. Then in another sheet convert it to a selection list in one cell ? Hutch, You can accomplish that using Data|Validation|Allow List. The List reference should be to a Named Range where the user inputs their data. You should be able to use a dynamic range that would allow for a variable list length using something similar to the formula below as the Name Definition =OFFSET($A$1,0,0,COUNTA($A:$A) To create the named range. (This formula would assume that there is no row heading and that there would be no o...

How do I display textboxes without any content in a spreadsheet?
I have an Excel workbook that is extremely slow in opening up and saving. It freezes up when I try to resize it and there is a time-lag when I scroll within certain worksheets. There are about 7 sheets within the workbook. Now, it came to my notice that some of the spreadsheets have many text boxes without any content in them. As a result, they are invisible until you click on them. My question is: Is there a way where I can have the spreadsheet show all the text boxes or a way that I could just delete all of them at once? Or if you think that there might be another alternative please...

How do you get to a format painter?
Hi not really sure what your question is? -- Regards Frank Kabel Frankfurt, Germany "Exodus" <Exodus@discussions.microsoft.com> schrieb im Newsbeitrag news:3110D7C7-4A88-4377-88AB-3BEB67AA3EB6@microsoft.com... > -- Don Guillett SalesAid Software donaldb@281.com "Exodus" <Exodus@discussions.microsoft.com> wrote in message news:3110D7C7-4A88-4377-88AB-3BEB67AA3EB6@microsoft.com... > If not already on your toolbar (looks like a paintbrush) then right click on the toolbar>customize>commands>format>find it>drag to toobar -- Don Guil...

Graph lines formatting in Excel 2007
I appear to have a couple of problems in Excel 2007 SP1 i) Although the help says you can reformat multiple lines by using the Ctrl button and the mouse, it doesn't seem to work .. I highlight the first line and when I try the second it deselects the first . Any ideas ii) I'm using Excel to display sets of spectra .... the default line thickness is 2.25pt and I want to set the default to 0.25 pt, how do I do it ?? iii) I resized a graph of 160 lines by hand (yawn, yawn) and saved it as a template ... however when I draw a new graph from the 160 lines and apply the temp...

why cut and insert cells only works randomly?
It seems that cutting and inserting cells in the spreadsheet, errors every other time and locks the excell spread sheet... Hi Tony, You'll have to be more specific, but you might find the answers to such problems as inserting rows, using OFFSET with formulas. And you will probably find why extending formulas and does not work for you. -- if any of those are the problem. http://www.mvps.org/dmcritchie/excel/insrtrow.htm I don't know what you mean by locking the sheet, have you turned on sheet protection or have merged cells in your copy.. --- HTH, David McRitchie, Microsoft...

copy cells which are horizontal to verticle
Hi, How do I copy a set of cells which are horizontal to verticle or vice versa? For example: A B C D E F G H I J 1 a 2 b 3 c 4 d 5 6 To become A B C D E F G H I J 1 a b c d 2 3 4 5 6 Thanks. You could copy the selection and then use paste special and check the transpose check box on the bottom right corner of the dialog box. Ed -- nuver ------------------------------------------------------------------------ nuver's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10036 View this thread: http://www.excelforum.com/show...

Refresh Display addon
I am currently trying to write an addon which uses the RefreshDisplay hook, but the hook does not appear to be firing. I am trying to use it just before an item is added. Does anyone else have this problem. I am using RMS 2.0 Thanks On Mon, 23 Apr 2007 03:44:00 -0700, Richard <Richard@discussions.microsoft.com> wrote: >I am currently trying to write an addon which uses the RefreshDisplay hook, >but the hook does not appear to be firing. > >I am trying to use it just before an item is added. > The RefreshDisplay hook is available only on POS, and through this event...

Excel 2007 weird cond formatting loss in some conditions w/DDE links
Hi. I have experienced a weird and serious loss of data while converting a financial spreadsheet from Excel 2003 to Excel 2007. I have a lot of conditional formatting in that sheet, and about 100 DDE links that works with a stock quote feed. After the conversion to v. 2007, everything was still there, but I kept losing all the conditional formatting data after closing and re-starting the spreadsheet (never while working on it). This problem repro'ed 100% of the time in the same conditions.. After a lot of trials and errors, I finally enabled the "Prompt user on automatic update fo...

how to pass a value from vb6 to a parameter in crystal
Hi, Does anyone know how to pass a value from vb6 to a parameter in crystal? Thanks & regards, edmond ...

How do I convert microsoft office into .ics format
How do I convert microsoft office into .ics format? I undertand how to export, but I need to import my data into another program that uses the .ics format (inbox.com) Henry;112372 Wrote: > How do I convert microsoft office into .ics format? I undertand how to > export, but I need to import my data into another program that uses the > .ics > format (inbox.com) Microsoft Office is a whole suite of applications: Outlook, Word, Excel, PowerPoint, Visio, Project, and Access. How would you propose converting applications into text files? -- Brian Tillman [M...

Content Control Placeholder Text formatting
Using VBA in Word 2007. I'm trying to add a content control into a form. I can get the formatting(Arial, 15) for the placeholder text set correctly unless the user enters some text and then deletes that text so the placeholder text gets re-shown. The placeholder text always goes back to the default formatting (Calibri, 11). This is a sample code that demonstrates this behavior. Can someone please tell me where my thinking is wrong? Sub test() Dim text As String text = "This is a test CC: " Dim ccText As String ccText = "enter the testCC text...

Date Format #3
How do you change the date format (ie. day-month-year to month-day-year) in Money? Money uses the Windows Regional Settings for the date format. You can find this in the Control Panel. "Philip" <ricoandcj@coxt.net> wrote in message news:043701c3899e$ac43d2f0$a001280a@phx.gbl... > How do you change the date format (ie. day-month-year to > month-day-year) in Money? Control Panel > Regional settings -- Regards Bob Peel, Microsoft MVP - Money Hints/Tips http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny UK Wishes/Suggestions mnyukwsh@mic...