Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Not Recognizing Decimal
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Not Recognizing Decimal
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Not Recognizing Decimal
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" wrote in message ... 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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Not Recognizing Decimal
"Ron" 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(Sele ction),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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Not Recognizing Decimal
"Ron" 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" wrote in message ... 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" wrote in message ... 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 - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Not Recognizing Decimal
PS....
"Ron" 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" wrote in message ... "Ron" 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" wrote in message ... 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" wrote in message ... 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 - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Not Recognizing Decimal
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(Sele ction), 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" wrote: PS.... "Ron" 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" wrote in message ... "Ron" 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" wrote in message ... 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" wrote in message .... 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 - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Not Recognizing Decimal
"Ron" wrote:
How does this look? [....] SumRng = WorksheetFunction.Round(WorksheetFunction.Sum(Sele ction), 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" wrote in message ... 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(Sele ction), 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" wrote: PS.... "Ron" 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" wrote in message ... "Ron" 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" wrote in message ... 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" wrote in message ... 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 - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Not Recognizing Decimal
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" wrote: "Ron" wrote: How does this look? [....] SumRng = WorksheetFunction.Round(WorksheetFunction.Sum(Sele ction), 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" wrote in message ... 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(Sele ction), 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" wrote: PS.... "Ron" 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" wrote in message ... "Ron" 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" wrote in message .... 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" wrote in message ... 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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
recognizing double digit numbers in code | Excel Discussion (Misc queries) | |||
Code Not Recognizing Value in Drop-Down Menu | Excel Programming | |||
Data Validation - Code Not Recognizing Something | Excel Programming | |||
Recognizing a whole number instead of a decimal | Excel Programming | |||
Recognizing something is being pasted in code. | Excel Programming |