Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sum returns Wrong Format
If Anyone could give me a hand....
Cell E33 value is "$2,793.70" Cell F33 value is "$450.00" My code rounds up the result and returns the value 3244. I need the value to be but I need it "3,243.70" What do I have to change in the code below? Thanks Sub sum() Dim MySum As Integer Dim MyRange As Range Set MyRange = ActiveSheet.Range("E33:F33") MySum = Application.WorksheetFunction.sum(MyRange) Range("F46").Value = MySum End Sub -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sum returns Wrong Format
If you are always going to use F46 as your destination cell, the simple
option is simply to format that cell. If your code may dynamically place results in difference cells and you need to format within VBA then the following might work for you. I suggest when it comes to things that you can do manually but are trying to replicate in code, use the macro recorder to give you a push in the right direction- that's where this code comes from. Either select the cell in code, or change the select statement to reflect your target range. Selection.NumberFormat = "#,##0.00" HTH, Keith "code_hungry" wrote in message ... If Anyone could give me a hand.... Cell E33 value is "$2,793.70" Cell F33 value is "$450.00" My code rounds up the result and returns the value 3244. I need the value to be but I need it "3,243.70" What do I have to change in the code below? Thanks Sub sum() Dim MySum As Integer Dim MyRange As Range Set MyRange = ActiveSheet.Range("E33:F33") MySum = Application.WorksheetFunction.sum(MyRange) Range("F46").Value = MySum End Sub -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sum returns Wrong Format
Dim MySum As Currency
"code_hungry" wrote: If Anyone could give me a hand.... Cell E33 value is "$2,793.70" Cell F33 value is "$450.00" My code rounds up the result and returns the value 3244. I need the value to be but I need it "3,243.70" What do I have to change in the code below? Thanks Sub sum() Dim MySum As Integer Dim MyRange As Range Set MyRange = ActiveSheet.Range("E33:F33") MySum = Application.WorksheetFunction.sum(MyRange) Range("F46").Value = MySum End Sub -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sum returns Wrong Format
I think if you change your Sub's last statement to this, instead of what you
have, it will do what you want... Range("F46").Value = FormatCurrency(MySum) Rick "code_hungry" wrote in message ... If Anyone could give me a hand.... Cell E33 value is "$2,793.70" Cell F33 value is "$450.00" My code rounds up the result and returns the value 3244. I need the value to be but I need it "3,243.70" What do I have to change in the code below? Thanks Sub sum() Dim MySum As Integer Dim MyRange As Range Set MyRange = ActiveSheet.Range("E33:F33") MySum = Application.WorksheetFunction.sum(MyRange) Range("F46").Value = MySum End Sub -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sum returns Wrong Format
You declared MySum as an Integer. Make it Double.
ps. I wouldn't use Sum for the procedure name. code_hungry wrote: If Anyone could give me a hand.... Cell E33 value is "$2,793.70" Cell F33 value is "$450.00" My code rounds up the result and returns the value 3244. I need the value to be but I need it "3,243.70" What do I have to change in the code below? Thanks Sub sum() Dim MySum As Integer Dim MyRange As Range Set MyRange = ActiveSheet.Range("E33:F33") MySum = Application.WorksheetFunction.sum(MyRange) Range("F46").Value = MySum End Sub -- -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sum returns Wrong Format
Well, I must say, I am surprised that worked. On the VBA side of things, the
Currency data type does not contain the currency symbol or nor the thousands separator (which can be seen by adding a Debug.Print MySum statement to the subroutine). While I am sure what you posted will do what the OP probably actually wanted, I would note that answers using your Dim statement will not be the same as those produced but the OP's original code. His Dim'ming the MySum variable as Integer would mean no decimal dollars are returned to the worksheet whereas your Currency data type will do so. Rick "JLGWhiz" wrote in message ... Dim MySum As Currency "code_hungry" wrote: If Anyone could give me a hand.... Cell E33 value is "$2,793.70" Cell F33 value is "$450.00" My code rounds up the result and returns the value 3244. I need the value to be but I need it "3,243.70" What do I have to change in the code below? Thanks Sub sum() Dim MySum As Integer Dim MyRange As Range Set MyRange = ActiveSheet.Range("E33:F33") MySum = Application.WorksheetFunction.sum(MyRange) Range("F46").Value = MySum End Sub -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sum returns Wrong Format
Thank you all for your reply. The Currency solution was the one that worked.
Because you were so quick to reply, I was able to give this project back to my boss on the same day. He was happy. Thanks again. "JLGWhiz" wrote: Dim MySum As Currency "code_hungry" wrote: If Anyone could give me a hand.... Cell E33 value is "$2,793.70" Cell F33 value is "$450.00" My code rounds up the result and returns the value 3244. I need the value to be but I need it "3,243.70" What do I have to change in the code below? Thanks Sub sum() Dim MySum As Integer Dim MyRange As Range Set MyRange = ActiveSheet.Range("E33:F33") MySum = Application.WorksheetFunction.sum(MyRange) Range("F46").Value = MySum End Sub -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sum returns Wrong Format
I would have used double.
code_hungry wrote: Thank you all for your reply. The Currency solution was the one that worked. Because you were so quick to reply, I was able to give this project back to my boss on the same day. He was happy. Thanks again. "JLGWhiz" wrote: Dim MySum As Currency "code_hungry" wrote: If Anyone could give me a hand.... Cell E33 value is "$2,793.70" Cell F33 value is "$450.00" My code rounds up the result and returns the value 3244. I need the value to be but I need it "3,243.70" What do I have to change in the code below? Thanks Sub sum() Dim MySum As Integer Dim MyRange As Range Set MyRange = ActiveSheet.Range("E33:F33") MySum = Application.WorksheetFunction.sum(MyRange) Range("F46").Value = MySum End Sub -- -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Strange? VBA code returns wrong date format (not a date) | Excel Programming | |||
cell returns wrong value | Excel Worksheet Functions | |||
Row() function returns wrong row and more.. | Excel Worksheet Functions | |||
Lookup returns wrong value | Excel Worksheet Functions | |||
SUM returns wrong result | Excel Worksheet Functions |