Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Str to Currency round problem
Hi,
I have a problem with currency rounding (Excel automatically rounds currency to 2 decimals). I have an InputBox in WorkBook_Open event where user enters the currency .... The procedure goes something like this: currencystr = InputBox("Please enter currency: ", "Currency ¤") If currencystr = "" Then Msgresult = MsgBox("No currency entered ... bla bla, enter ... bla ...", vbOKOnly,"Error") Exit sub Else currency = currencystr Sheets(1).Cells(1,1).NumberFormat = "#,##0.0000 $" Sheets(1).Cells(1,1).Value = Currency end if Well, the problem is that the value in the Cells(1,1) is always rounded to 2 decimals and I need 4 ! If I enter 7,4791 Excel rounds it up to 7,5000, and if I enter 7,2319 Excel shows 7,2300 ! I googled but couldn't find the solution ... can anyone help please ? Thanks in advance ! -- Pozdrav Revenger 29.01.2007 09:07:57 Jednom su proizveli Chuck Norris toalet papir, ali papir nije dopustao da itko sere po njemu. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Str to Currency round problem
Try using double instread of the currency data type.
Also currency = currencystr should produce an error because currency is a reserved word. You may also want to check for numerical values on your input. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Revenger" wrote: Hi, I have a problem with currency rounding (Excel automatically rounds currency to 2 decimals). I have an InputBox in WorkBook_Open event where user enters the currency .... The procedure goes something like this: currencystr = InputBox("Please enter currency: ", "Currency ‚¬") If currencystr = "" Then Msgresult = MsgBox("No currency entered ... bla bla, enter ... bla ...", vbOKOnly,"Error") Exit sub Else currency = currencystr Sheets(1).Cells(1,1).NumberFormat = "#,##0.0000 $" Sheets(1).Cells(1,1).Value = Currency end if Well, the problem is that the value in the Cells(1,1) is always rounded to 2 decimals and I need 4 ! If I enter 7,4791 Excel rounds it up to 7,5000, and if I enter 7,2319 Excel shows 7,2300 ! I googled but couldn't find the solution ... can anyone help please ? Thanks in advance ! -- Pozdrav Revenger 29.01.2007 09:07:57 Jednom su proizveli Chuck Norris toalet papir, ali papir nije dopustao da itko sere po njemu. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Str to Currency round problem
On Mon, 29 Jan 2007 03:09:01 -0800, Martin Fishlock wrote:
Try using double instread of the currency data type. Also currency = currencystr should produce an error because currency is a reserved word. You may also want to check for numerical values on your input. I've resolved the issue ... I didn't use currency as variable name, my code is not in "english" so I quickly typed "translated" version of my code ... The problem was with decimal simbol, since my decimal simbol is "," and digit grouping symbol is "." but VBA wont work like that. So i had to replace the "." and "," symbols in currencystr and then write the value to the cell. Thanks for the reply ! -- Pozdrav Revenger 29.01.2007 15:27:43 Jednom su proizveli Chuck Norris toalet papir, ali papir nije dopustao da itko sere po njemu. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Str to Currency round problem
A numberformat of currency can be troublesome.
This worked for me with my USA settings: Option Explicit Sub testme() Dim CurrencyStr As String Dim myCurrency As Currency CurrencyStr = InputBox("Please enter currency: ", "Currency ¤") If CurrencyStr = "" Then MsgBox "No currency entered ... bla bla, enter ... bla ...", _ vbOKOnly, "Error" Exit Sub Else If IsNumeric(CurrencyStr) Then myCurrency = CurrencyStr Sheets(1).Cells(1, 1).NumberFormat = "#,##0.0000 $" Sheets(1).Cells(1, 1).Value2 = myCurrency Else Beep End If End If End Sub Just in case you find that you still had trouble. (Note the .value2 property.) Revenger wrote: On Mon, 29 Jan 2007 03:09:01 -0800, Martin Fishlock wrote: Try using double instread of the currency data type. Also currency = currencystr should produce an error because currency is a reserved word. You may also want to check for numerical values on your input. I've resolved the issue ... I didn't use currency as variable name, my code is not in "english" so I quickly typed "translated" version of my code ... The problem was with decimal simbol, since my decimal simbol is "," and digit grouping symbol is "." but VBA wont work like that. So i had to replace the "." and "," symbols in currencystr and then write the value to the cell. Thanks for the reply ! -- Pozdrav Revenger 29.01.2007 15:27:43 Jednom su proizveli Chuck Norris toalet papir, ali papir nije dopustao da itko sere po njemu. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Str to Currency round problem
On Mon, 29 Jan 2007 08:46:17 -0600, Dave Peterson wrote:
Sheets(1).Cells(1, 1).Value2 = myCurrency Just in case you find that you still had trouble. (Note the .value2 property.) Thanks for the reply, I've got it working now, but this is a good tip (.value2) ... Thanks for the tip ... didn't know that ... -- Pozdrav Revenger 30.01.2007 10:27:25 Jednom su proizveli Chuck Norris toalet papir, ali papir nije dopustao da itko sere po njemu. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
currency Problem | Excel Worksheet Functions | |||
Is there a way to round currency? | Excel Discussion (Misc queries) | |||
Currency formating problem | Excel Discussion (Misc queries) | |||
Currency formating problem | Excel Discussion (Misc queries) | |||
I am trying to round currency to the closest .50, can anyone tell. | Excel Discussion (Misc queries) |