VBA Sum returns Wrong Format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

code_hungry - 25 Jan 2008 17:10 GMT

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
--

Reply to this Message

Keith R - 25 Jan 2008 17:23 GMT

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

> If Anyone could give me a hand....
>
[quoted text clipped - 16 lines]
>
> End Sub

Reply to this Message

JLGWhiz - 25 Jan 2008 17:25 GMT

Dim MySum As Currency

> If Anyone could give me a hand....
>
[quoted text clipped - 16 lines]
>
> End Sub

Reply to this Message

Rick Rothstein (MVP - VB) - 25 Jan 2008 17:40 GMT

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

> Dim MySum As Currency
>
[quoted text clipped - 19 lines]
>>
>> End Sub

Reply to this Message

code_hungry - 25 Jan 2008 21:52 GMT

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.

> Dim MySum As Currency
>
[quoted text clipped - 18 lines]
> >
> > End Sub

Reply to this Message

Dave Peterson - 25 Jan 2008 23:03 GMT

I would have used double.

> 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
[quoted text clipped - 25 lines]
> > > End Sub
> > > --

Signature

Dave Peterson

Reply to this Message

Rick Rothstein (MVP - VB) - 25 Jan 2008 17:31 GMT

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

> If Anyone could give me a hand....
>
[quoted text clipped - 16 lines]
>
> End Sub

Reply to this Message

Dave Peterson - 25 Jan 2008 17:36 GMT

You declared MySum as an Integer. Make it Double.

ps. I wouldn't use Sum for the procedure name.

> If Anyone could give me a hand....
>
[quoted text clipped - 17 lines]
> End Sub
> --

Signature

Dave Peterson

Reply to this Message