Hello all,

Anyone have any ideas why this code does not recognize the decimal.
I'm trying to test a column to make sure it equals zero. However for
some reason this code does not recognize the trailing decimals. When
I change IF SUMRNG <> 0 then to IF SUMRNG <> 0.00 VBA alters it to
0#. If the column is out of balance .49 cents it does not activate
the MSGBOX but if it's out .50 cents then the MSGBOX is activated.
Appreciate your assistance, Ron

With Selection
SumRng = Application.WorksheetFunction.Sum(Selection)
If SumRng <> 0 Then
MsgBox "Out of Balance, Please review and make the necessary
corrections. Balance should equal ZERO. " & SumRng

End If
End With

Did you declare SumRng As Long. Try -
Dim SumRng as Double

The # after a value tells the compiler to treat it as a Double, rather than
as an Integer in the case of a non decimal.

Regards,
Peter T



"Ron" <(E-Mail Removed)> wrote in message
news:bfc255ed-9d5c-4821-9ec4-(E-Mail Removed)...
> Hello all,
>
> Anyone have any ideas why this code does not recognize the decimal.
> I'm trying to test a column to make sure it equals zero. However for
> some reason this code does not recognize the trailing decimals. When
> I change IF SUMRNG <> 0 then to IF SUMRNG <> 0.00 VBA alters it to
> 0#. If the column is out of balance .49 cents it does not activate
> the MSGBOX but if it's out .50 cents then the MSGBOX is activated.
> Appreciate your assistance, Ron
>
> With Selection
> SumRng = Application.WorksheetFunction.Sum(Selection)
> If SumRng <> 0 Then
> MsgBox "Out of Balance, Please review and make the necessary
> corrections. Balance should equal ZERO. " & SumRng
>
> End If
> End With

Hi Peter, You were spot on with the SumRng as Double. But I'm having
a little problem getting my code to work properly. For some reason
there are trailing decimals that I am told are binary values. How do
I deal with them? I've tried Rounding with no results. My goal is to
have the column equal Zero or, at least within a penny, but I'm
getting this instead -0.00000000006984990. After changing SumRng to
double I get something like this in the MSGBOX-6.984990. I think my
problem is the binary values. Thanks for your assistance, Ron



On Jul 27, 3:30*pm, "Peter T" <peter_t@discussions> wrote:
> Did you declare SumRng As Long. Try -
> Dim SumRng as Double
>
> The # after a value tells the compiler to treat it as a Double, rather than
> as an Integer in the case of a non decimal.
>
> Regards,
> Peter T
>
> "Ron" <whasupwit...@verizon.net> wrote in message
>
> news:bfc255ed-9d5c-4821-9ec4-(E-Mail Removed)...
>
>
>
> > Hello all,
>
> > Anyone have any ideas why this code does not recognize the decimal.
> > I'm trying to test a column to make sure it equals zero. *However for
> > some reason this code does not recognize the trailing decimals. *When
> > I change IF SUMRNG <> 0 then to IF SUMRNG <> 0.00 VBA alters it to
> > 0#. * If the column is out of balance .49 cents it does not activate
> > the MSGBOX but if it's out .50 cents then the MSGBOX is activated.
> > Appreciate your assistance, Ron
>
> > With Selection
> > SumRng = Application.WorksheetFunction.Sum(Selection)
> > If SumRng <> 0 Then
> > MsgBox "Out of Balance, Please review and make the necessary
> > corrections. Balance should equal ZERO. " & SumRng
>
> > End If
> > End With- Hide quoted text -
>
> - Show quoted text -

"Ron" <(E-Mail Removed)> wrote:
> If the column is out of balance .49 cents it does not activate
> the MSGBOX but if it's out .50 cents then the MSGBOX is activated.

It does not matter whether you do SumRng<>0 or SumRng<>0.0.

But what is the type of SumRng?

I suspect it is Long or Integer. It should be Variant, Currency or Double.
(I prefer Double.)

FYI, I think it would be more prudent to compute:

SumRng = WorksheetFunction.Round(WorksheetFunction.Sum(Selection),2)

which could be written:

With WorksheetFunction
SumRng = .Round(.Sum(Selection),2)
End With

That ameliorates anomalies that arise due to the internal binary arithmetic,
which often causes small decimal fraction differences from what you see when
a cell is formatted with 2 decimal places.

Of course, the use of ROUND() will also mask more significant differences as
well. If that is a concern, then compute SumRng as you did, but do a
comparison like:

If Abs(SumRng) < 0.00001 Then

selecting an appropriate value for 0.00001 that satisfies your degree of
tolerance.

BTW, I use WorksheetFunction.Round (Excel ROUND) instead of VBA Round()
because the latter rounds differently than Excel does. See the description
of the CInt and CLng functions for details.


----- original message -----

"Ron" <(E-Mail Removed)> wrote in message
news:bfc255ed-9d5c-4821-9ec4-(E-Mail Removed)...
> Hello all,
>
> Anyone have any ideas why this code does not recognize the decimal.
> I'm trying to test a column to make sure it equals zero. However for
> some reason this code does not recognize the trailing decimals. When
> I change IF SUMRNG <> 0 then to IF SUMRNG <> 0.00 VBA alters it to
> 0#. If the column is out of balance .49 cents it does not activate
> the MSGBOX but if it's out .50 cents then the MSGBOX is activated.
> Appreciate your assistance, Ron
>
> With Selection
> SumRng = Application.WorksheetFunction.Sum(Selection)
> If SumRng <> 0 Then
> MsgBox "Out of Balance, Please review and make the necessary
> corrections. Balance should equal ZERO. " & SumRng
>
> End If
> End With

"Ron" <(E-Mail Removed)> wrote:
> I've tried Rounding with no results.
> My goal is to have the column equal Zero or,
> at least within a penny, but I'm getting this
> instead -0.00000000006984990.

My response to your original inquiry anticipated this. Have a look at it.

But you say that rounding did not remedy the problem. It certainly should
have.

If my previous posting does not answer your question, please post your
rounding code, including all relevant declarations.


----- original message -----

"Ron" <(E-Mail Removed)> wrote in message
news:327105c8-8532-4d58-bf0f-(E-Mail Removed)...
Hi Peter, You were spot on with the SumRng as Double. But I'm having
a little problem getting my code to work properly. For some reason
there are trailing decimals that I am told are binary values. How do
I deal with them? I've tried Rounding with no results. My goal is to
have the column equal Zero or, at least within a penny, but I'm
getting this instead -0.00000000006984990. After changing SumRng to
double I get something like this in the MSGBOX-6.984990. I think my
problem is the binary values. Thanks for your assistance, Ron



On Jul 27, 3:30 pm, "Peter T" <peter_t@discussions> wrote:
> Did you declare SumRng As Long. Try -
> Dim SumRng as Double
>
> The # after a value tells the compiler to treat it as a Double, rather
> than
> as an Integer in the case of a non decimal.
>
> Regards,
> Peter T
>
> "Ron" <whasupwit...@verizon.net> wrote in message
>
> news:bfc255ed-9d5c-4821-9ec4-(E-Mail Removed)...
>
>
>
> > Hello all,
>
> > Anyone have any ideas why this code does not recognize the decimal.
> > I'm trying to test a column to make sure it equals zero. However for
> > some reason this code does not recognize the trailing decimals. When
> > I change IF SUMRNG <> 0 then to IF SUMRNG <> 0.00 VBA alters it to
> > 0#. If the column is out of balance .49 cents it does not activate
> > the MSGBOX but if it's out .50 cents then the MSGBOX is activated.
> > Appreciate your assistance, Ron
>
> > With Selection
> > SumRng = Application.WorksheetFunction.Sum(Selection)
> > If SumRng <> 0 Then
> > MsgBox "Out of Balance, Please review and make the necessary
> > corrections. Balance should equal ZERO. " & SumRng
>
> > End If
> > End With- Hide quoted text -
>
> - Show quoted text -

PS....

"Ron" <(E-Mail Removed)> wrote:
> My goal is to have the column equal Zero or,
> at least within a penny, but I'm getting this
> instead -0.00000000006984990.

Although simply rounding the sum might mask this particular abberation, it
would be prudent to use ROUND liberally in most financial calculations in
the Excel spreadsheet as well. That will minimize propagating such
numerical abberations into other spreadsheet calculations. Even formulas as
innocuous as =A1-A2 can introduce annoying numerical abberations.


----- original message -----

"JoeU2004" <(E-Mail Removed)> wrote in message
news:u$(E-Mail Removed)...
> "Ron" <(E-Mail Removed)> wrote:
>> I've tried Rounding with no results.
>> My goal is to have the column equal Zero or,
>> at least within a penny, but I'm getting this
>> instead -0.00000000006984990.
>
> My response to your original inquiry anticipated this. Have a look at it.
>
> But you say that rounding did not remedy the problem. It certainly should
> have.
>
> If my previous posting does not answer your question, please post your
> rounding code, including all relevant declarations.
>
>
> ----- original message -----
>
> "Ron" <(E-Mail Removed)> wrote in message
> news:327105c8-8532-4d58-bf0f-(E-Mail Removed)...
> Hi Peter, You were spot on with the SumRng as Double. But I'm having
> a little problem getting my code to work properly. For some reason
> there are trailing decimals that I am told are binary values. How do
> I deal with them? I've tried Rounding with no results. My goal is to
> have the column equal Zero or, at least within a penny, but I'm
> getting this instead -0.00000000006984990. After changing SumRng to
> double I get something like this in the MSGBOX-6.984990. I think my
> problem is the binary values. Thanks for your assistance, Ron
>
>
>
> On Jul 27, 3:30 pm, "Peter T" <peter_t@discussions> wrote:
>> Did you declare SumRng As Long. Try -
>> Dim SumRng as Double
>>
>> The # after a value tells the compiler to treat it as a Double, rather
>> than
>> as an Integer in the case of a non decimal.
>>
>> Regards,
>> Peter T
>>
>> "Ron" <whasupwit...@verizon.net> wrote in message
>>
>> news:bfc255ed-9d5c-4821-9ec4-(E-Mail Removed)...
>>
>>
>>
>> > Hello all,
>>
>> > Anyone have any ideas why this code does not recognize the decimal.
>> > I'm trying to test a column to make sure it equals zero. However for
>> > some reason this code does not recognize the trailing decimals. When
>> > I change IF SUMRNG <> 0 then to IF SUMRNG <> 0.00 VBA alters it to
>> > 0#. If the column is out of balance .49 cents it does not activate
>> > the MSGBOX but if it's out .50 cents then the MSGBOX is activated.
>> > Appreciate your assistance, Ron
>>
>> > With Selection
>> > SumRng = Application.WorksheetFunction.Sum(Selection)
>> > If SumRng <> 0 Then
>> > MsgBox "Out of Balance, Please review and make the necessary
>> > corrections. Balance should equal ZERO. " & SumRng
>>
>> > End If
>> > End With- Hide quoted text -
>>
>> - Show quoted text -
>

Hi JoeU2004, thanks to you I think I have it working properly. I
really like your approach to the rounding issue and thanks for the
heads up on Rounding. How does this look?


Sub JrnlValidationCode()
Dim mybook As Workbook
Dim ValBook As Workbook
Dim SumRng As Double
Set mybook = ActiveWorkbook

Application.ScreenUpdating = False
ActiveSheet.Unprotect

Range("E12").Select
Range(Selection, Selection.End(xlDown)).Offset(0, 11).Select

With Selection
SumRng = WorksheetFunction.Round(WorksheetFunction.Sum(Selection), 2)

With WorksheetFunction
SumRng = .Round(.Sum(Selection), 2)
End With

If SumRng <> 0# Then
MsgBox prompt:="Out of Balance, Please make the necessary corrections"
_
& vbNewLine & _
"and re-run Validation to complete the validation process." _
& vbNewLine & vbNewLine & _
"Balance should equal ZERO." _
& vbNewLine & vbNewLine & _
"Amount column is out of Balance! " & "$" & SumRng, _
Title:="Amount Column Status"
Exit Sub
End If
End With

'validates columns to meet PeopleSoft requirements.
Range("E12").Select
ValDataI
ValDataK
ValDataN
ValDataW
ValDataAA
ValDataAB
ValDataAI
FindRedCell
Validate
LockCells

ActiveSheet.Protect

End Sub




On Jul 27, 4:27*pm, "JoeU2004" <joeu2...@hotmail.com> wrote:
> PS....
>
> "Ron" <whasupwit...@verizon.net> wrote:
> > My goal is to have the column equal Zero or,
> > at least within a penny, but I'm getting this
> > instead -0.00000000006984990.
>
> Although simply rounding the sum might mask this particular abberation, it
> would be prudent to use ROUND liberally in most financial calculations in
> the Excel spreadsheet as well. *That will minimize propagating such
> numerical abberations into other spreadsheet calculations. *Even formulas as
> innocuous as =A1-A2 can introduce annoying numerical abberations.
>
> ----- original message -----
>
> "JoeU2004" <joeu2...@hotmail.com> wrote in message
>
> news:u$(E-Mail Removed)...
>
>
>
> > "Ron" <whasupwit...@verizon.net> wrote:
> >> I've tried Rounding with no results.
> >> My goal is to have the column equal Zero or,
> >> at least within a penny, but I'm getting this
> >> instead -0.00000000006984990.
>
> > My response to your original inquiry anticipated this. *Have a look at it.
>
> > But you say that rounding did not remedy the problem. *It certainly should
> > have.
>
> > If my previous posting does not answer your question, please post your
> > rounding code, including all relevant declarations.
>
> > ----- original message -----
>
> > "Ron" <whasupwit...@verizon.net> wrote in message
> >news:327105c8-8532-4d58-bf0f-(E-Mail Removed)...
> > Hi Peter, You were spot on with the SumRng as Double. *But I'm having
> > a little problem getting my code to work properly. *For some reason
> > there are trailing decimals that I am told are binary values. *How do
> > I deal with them? *I've tried Rounding with no results. *My goal isto
> > have the column equal Zero or, at least within a penny, but I'm
> > getting this instead -0.00000000006984990. *After changing SumRng to
> > double I get something like this in the MSGBOX-6.984990. *I think my
> > problem is the binary values. *Thanks for your assistance, Ron
>
> > On Jul 27, 3:30 pm, "Peter T" <peter_t@discussions> wrote:
> >> Did you declare SumRng As Long. Try -
> >> Dim SumRng as Double
>
> >> The # after a value tells the compiler to treat it as a Double, rather
> >> than
> >> as an Integer in the case of a non decimal.
>
> >> Regards,
> >> Peter T
>
> >> "Ron" <whasupwit...@verizon.net> wrote in message
>
> >>news:bfc255ed-9d5c-4821-9ec4-(E-Mail Removed)....
>
> >> > Hello all,
>
> >> > Anyone have any ideas why this code does not recognize the decimal.
> >> > I'm trying to test a column to make sure it equals zero. However for
> >> > some reason this code does not recognize the trailing decimals. When
> >> > I change IF SUMRNG <> 0 then to IF SUMRNG <> 0.00 VBA alters it to
> >> > 0#. If the column is out of balance .49 cents it does not activate
> >> > the MSGBOX but if it's out .50 cents then the MSGBOX is activated.
> >> > Appreciate your assistance, Ron
>
> >> > With Selection
> >> > SumRng = Application.WorksheetFunction.Sum(Selection)
> >> > If SumRng <> 0 Then
> >> > MsgBox "Out of Balance, Please review and make the necessary
> >> > corrections. Balance should equal ZERO. " & SumRng
>
> >> > End If
> >> > End With- Hide quoted text -
>
> >> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

"Ron" <(E-Mail Removed)> wrote:
> How does this look?
> [....]
> SumRng = WorksheetFunction.Round(WorksheetFunction.Sum(Selection), 2)
> With WorksheetFunction
> SumRng = .Round(.Sum(Selection), 2)
> End With

No need to sum and round twice. It doesn't get any better .

These are just two ways to do the same thing. "With WorksheetFunction"
saves you the trouble of typing "WorksheetFunction" twice in the same
statement. Since you've done it already, I would get rid of the "With
WorksheetFunction" form.


FYI, I see no reason to do "With Selection" in this code snippet.

Of course, if you have more references to Selection objects/methods that you
are not showing here (understandably), that's fine. Otherwise, you might
consider getting rid of the "With Selection".

And it appears that you are missing the statement
Application.ScreenUpdating=True at the end of macro.

Again, the "omission" might be because this code snippet is part of a larger
code, much of which you prudently omitted from your posting so as to focus
on the relevant issues. Just thought I'd bring it to your attention.

One omission seems to be a definite defect, though: you do not have
ActiveSheet.Protect and ScreenUpdating=True statements before the Exit Sub
within the If statement.


----- original message -----

"Ron" <(E-Mail Removed)> wrote in message
news:a062bc54-c1ca-494a-95df-(E-Mail Removed)...
Hi JoeU2004, thanks to you I think I have it working properly. I
really like your approach to the rounding issue and thanks for the
heads up on Rounding. How does this look?


Sub JrnlValidationCode()
Dim mybook As Workbook
Dim ValBook As Workbook
Dim SumRng As Double
Set mybook = ActiveWorkbook

Application.ScreenUpdating = False
ActiveSheet.Unprotect

Range("E12").Select
Range(Selection, Selection.End(xlDown)).Offset(0, 11).Select

With Selection
SumRng = WorksheetFunction.Round(WorksheetFunction.Sum(Selection), 2)

With WorksheetFunction
SumRng = .Round(.Sum(Selection), 2)
End With

If SumRng <> 0# Then
MsgBox prompt:="Out of Balance, Please make the necessary corrections"
_
& vbNewLine & _
"and re-run Validation to complete the validation process." _
& vbNewLine & vbNewLine & _
"Balance should equal ZERO." _
& vbNewLine & vbNewLine & _
"Amount column is out of Balance! " & "$" & SumRng, _
Title:="Amount Column Status"
Exit Sub
End If
End With

'validates columns to meet PeopleSoft requirements.
Range("E12").Select
ValDataI
ValDataK
ValDataN
ValDataW
ValDataAA
ValDataAB
ValDataAI
FindRedCell
Validate
LockCells

ActiveSheet.Protect

End Sub




On Jul 27, 4:27 pm, "JoeU2004" <joeu2...@hotmail.com> wrote:
> PS....
>
> "Ron" <whasupwit...@verizon.net> wrote:
> > My goal is to have the column equal Zero or,
> > at least within a penny, but I'm getting this
> > instead -0.00000000006984990.
>
> Although simply rounding the sum might mask this particular abberation, it
> would be prudent to use ROUND liberally in most financial calculations in
> the Excel spreadsheet as well. That will minimize propagating such
> numerical abberations into other spreadsheet calculations. Even formulas
> as
> innocuous as =A1-A2 can introduce annoying numerical abberations.
>
> ----- original message -----
>
> "JoeU2004" <joeu2...@hotmail.com> wrote in message
>
> news:u$(E-Mail Removed)...
>
>
>
> > "Ron" <whasupwit...@verizon.net> wrote:
> >> I've tried Rounding with no results.
> >> My goal is to have the column equal Zero or,
> >> at least within a penny, but I'm getting this
> >> instead -0.00000000006984990.
>
> > My response to your original inquiry anticipated this. Have a look at
> > it.
>
> > But you say that rounding did not remedy the problem. It certainly
> > should
> > have.
>
> > If my previous posting does not answer your question, please post your
> > rounding code, including all relevant declarations.
>
> > ----- original message -----
>
> > "Ron" <whasupwit...@verizon.net> wrote in message
> >news:327105c8-8532-4d58-bf0f-(E-Mail Removed)...
> > Hi Peter, You were spot on with the SumRng as Double. But I'm having
> > a little problem getting my code to work properly. For some reason
> > there are trailing decimals that I am told are binary values. How do
> > I deal with them? I've tried Rounding with no results. My goal is to
> > have the column equal Zero or, at least within a penny, but I'm
> > getting this instead -0.00000000006984990. After changing SumRng to
> > double I get something like this in the MSGBOX-6.984990. I think my
> > problem is the binary values. Thanks for your assistance, Ron
>
> > On Jul 27, 3:30 pm, "Peter T" <peter_t@discussions> wrote:
> >> Did you declare SumRng As Long. Try -
> >> Dim SumRng as Double
>
> >> The # after a value tells the compiler to treat it as a Double, rather
> >> than
> >> as an Integer in the case of a non decimal.
>
> >> Regards,
> >> Peter T
>
> >> "Ron" <whasupwit...@verizon.net> wrote in message
>
> >>news:bfc255ed-9d5c-4821-9ec4-(E-Mail Removed)...
>
> >> > Hello all,
>
> >> > Anyone have any ideas why this code does not recognize the decimal.
> >> > I'm trying to test a column to make sure it equals zero. However for
> >> > some reason this code does not recognize the trailing decimals. When
> >> > I change IF SUMRNG <> 0 then to IF SUMRNG <> 0.00 VBA alters it to
> >> > 0#. If the column is out of balance .49 cents it does not activate
> >> > the MSGBOX but if it's out .50 cents then the MSGBOX is activated.
> >> > Appreciate your assistance, Ron
>
> >> > With Selection
> >> > SumRng = Application.WorksheetFunction.Sum(Selection)
> >> > If SumRng <> 0 Then
> >> > MsgBox "Out of Balance, Please review and make the necessary
> >> > corrections. Balance should equal ZERO. " & SumRng
>
> >> > End If
> >> > End With- Hide quoted text -
>
> >> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Hi Joe, thank you for your assistance and sharing your expertise with
VBA. I made the changes you recommended and it runs great. Thanks,
Ron


On Jul 27, 8:17*pm, "JoeU2004" <joeu2...@hotmail.com> wrote:
> "Ron" <whasupwit...@verizon.net> wrote:
> > How does this look?
> > [....]
> > SumRng = WorksheetFunction.Round(WorksheetFunction.Sum(Selection), 2)
> > With WorksheetFunction
> > SumRng = .Round(.Sum(Selection), 2)
> > End With
>
> No need to sum and round twice. *It doesn't get any better .
>
> These are just two ways to do the same thing. *"With WorksheetFunction"
> saves you the trouble of typing "WorksheetFunction" twice in the same
> statement. *Since you've done it already, I would get rid of the "With
> WorksheetFunction" form.
>
> FYI, I see no reason to do "With Selection" in this code snippet.
>
> Of course, if you have more references to Selection objects/methods that you
> are not showing here (understandably), that's fine. *Otherwise, you might
> consider getting rid of the "With Selection".
>
> And it appears that you are missing the statement
> Application.ScreenUpdating=True at the end of macro.
>
> Again, the "omission" might be because this code snippet is part of a larger
> code, much of which you prudently omitted from your posting so as to focus
> on the relevant issues. *Just thought I'd bring it to your attention.
>
> One omission seems to be a definite defect, though: *you do not have
> ActiveSheet.Protect and ScreenUpdating=True statements before the Exit Sub
> within the If statement.
>
> ----- original message -----
>
> "Ron" <whasupwit...@verizon.net> wrote in message
>
> news:a062bc54-c1ca-494a-95df-(E-Mail Removed)...
> Hi JoeU2004, thanks to you I think I have it working properly. *I
> really like your approach to the rounding issue and thanks for the
> heads up on Rounding. *How does this look?
>
> Sub JrnlValidationCode()
> Dim mybook As Workbook
> Dim ValBook As Workbook
> Dim SumRng As Double
> Set mybook = ActiveWorkbook
>
> Application.ScreenUpdating = False
> ActiveSheet.Unprotect
>
> Range("E12").Select
> Range(Selection, Selection.End(xlDown)).Offset(0, 11).Select
>
> With Selection
> SumRng = WorksheetFunction.Round(WorksheetFunction.Sum(Selection), 2)
>
> With WorksheetFunction
> SumRng = .Round(.Sum(Selection), 2)
> End With
>
> If SumRng <> 0# Then
> MsgBox prompt:="Out of Balance, Please make the necessary corrections"
> _
> & vbNewLine & _
> "and re-run Validation to complete the validation process." _
> & vbNewLine & vbNewLine & _
> "Balance should equal ZERO." _
> & vbNewLine & vbNewLine & _
> "Amount column is out of Balance! " & "$" & SumRng, _
> Title:="Amount Column Status"
> Exit Sub
> End If
> End With
>
> 'validates columns to meet PeopleSoft requirements.
> Range("E12").Select
> ValDataI
> ValDataK
> ValDataN
> ValDataW
> ValDataAA
> ValDataAB
> ValDataAI
> FindRedCell
> Validate
> LockCells
>
> ActiveSheet.Protect
>
> End Sub
>
> On Jul 27, 4:27 pm, "JoeU2004" <joeu2...@hotmail.com> wrote:
>
>
>
> > PS....
>
> > "Ron" <whasupwit...@verizon.net> wrote:
> > > My goal is to have the column equal Zero or,
> > > at least within a penny, but I'm getting this
> > > instead -0.00000000006984990.
>
> > Although simply rounding the sum might mask this particular abberation,it
> > would be prudent to use ROUND liberally in most financial calculations in
> > the Excel spreadsheet as well. That will minimize propagating such
> > numerical abberations into other spreadsheet calculations. Even formulas
> > as
> > innocuous as =A1-A2 can introduce annoying numerical abberations.
>
> > ----- original message -----
>
> > "JoeU2004" <joeu2...@hotmail.com> wrote in message
>
> >news:u$(E-Mail Removed)...
>
> > > "Ron" <whasupwit...@verizon.net> wrote:
> > >> I've tried Rounding with no results.
> > >> My goal is to have the column equal Zero or,
> > >> at least within a penny, but I'm getting this
> > >> instead -0.00000000006984990.
>
> > > My response to your original inquiry anticipated this. Have a look at
> > > it.
>
> > > But you say that rounding did not remedy the problem. It certainly
> > > should
> > > have.
>
> > > If my previous posting does not answer your question, please post your
> > > rounding code, including all relevant declarations.
>
> > > ----- original message -----
>
> > > "Ron" <whasupwit...@verizon.net> wrote in message
> > >news:327105c8-8532-4d58-bf0f-(E-Mail Removed)....
> > > Hi Peter, You were spot on with the SumRng as Double. But I'm having
> > > a little problem getting my code to work properly. For some reason
> > > there are trailing decimals that I am told are binary values. How do
> > > I deal with them? I've tried Rounding with no results. My goal is to
> > > have the column equal Zero or, at least within a penny, but I'm
> > > getting this instead -0.00000000006984990. After changing SumRng to
> > > double I get something like this in the MSGBOX-6.984990. I think my
> > > problem is the binary values. Thanks for your assistance, Ron
>
> > > On Jul 27, 3:30 pm, "Peter T" <peter_t@discussions> wrote:
> > >> Did you declare SumRng As Long. Try -
> > >> Dim SumRng as Double
>
> > >> The # after a value tells the compiler to treat it as a Double, rather
> > >> than
> > >> as an Integer in the case of a non decimal.
>
> > >> Regards,
> > >> Peter T
>
> > >> "Ron" <whasupwit...@verizon.net> wrote in message
>
> > >>news:bfc255ed-9d5c-4821-9ec4-(E-Mail Removed)...
>
> > >> > Hello all,
>
> > >> > Anyone have any ideas why this code does not recognize the decimal..
> > >> > I'm trying to test a column to make sure it equals zero. However for
> > >> > some reason this code does not recognize the trailing decimals. When
> > >> > I change IF SUMRNG <> 0 then to IF SUMRNG <> 0.00 VBA alters it to
> > >> > 0#. If the column is out of balance .49 cents it does not activate
> > >> > the MSGBOX but if it's out .50 cents then the MSGBOX is activated.
> > >> > Appreciate your assistance, Ron
>
> > >> > With Selection
> > >> > SumRng = Application.WorksheetFunction.Sum(Selection)
> > >> > If SumRng <> 0 Then
> > >> > MsgBox "Out of Balance, Please review and make the necessary
> > >> > corrections. Balance should equal ZERO. " & SumRng
>
> > >> > End If
> > >> > End With- Hide quoted text -
>
> > >> - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -