Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    My range M2:M6 all show zero, so why am I getting the error dialogue? Can this be adapted to ignore partial pennies?

    Code:
    If Worksheets("Summary").Range("M2") <> 0 Or _
    	Worksheets("Summary").Range("M3") <> 0 Or _
    	Worksheets("Summary").Range("M4") <> 0 Or _
    	Worksheets("Summary").Range("M5") <> 0 Or _
    	Worksheets("Summary").Range("M6") <> 0 Then
    		MsgBox "Unable to proceed until balanced to zero.", vbCritical
    		Exit Sub
    End If

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [quote name='VegasNath' post='778508' date='05-Jun-2009 13:37']My range M2:M6 all show zero, so why am I getting the error dialogue? Can this be adapted to ignore partial pennies?

    Code:
    If Worksheets("Summary").Range("M2") <> 0 Or _
    	Worksheets("Summary").Range("M3") <> 0 Or _
    	Worksheets("Summary").Range("M4") <> 0 Or _
    	Worksheets("Summary").Range("M5") <> 0 Or _
    	Worksheets("Summary").Range("M6") <> 0 Then
    		MsgBox "Unable to proceed until balanced to zero.", vbCritical
    		Exit Sub
    End If
    [/quote]

    Try This

    Sub MyCheck()
    If Abs(Worksheets("Summary").Range("M2")) > 1 Or _
    Abs(Worksheets("Summary").Range("M3")) > 1 Or _
    Abs(Worksheets("Summary").Range("M4")) > 1 Or _
    Abs(Worksheets("Summary").Range("M5")) > 1 Or _
    Abs(Worksheets("Summary").Range("M6")) > 1 Then
    MsgBox "Unable to Proceed until balance is zero.", vbCritical
    Exit Sub
    End If
    End Sub

    Regards,

    Tom Duthie

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You could round the values to a certain number of sig figures to avoid these potential rounding errors
    for example using something like:

    round(Worksheets("Summary").Range("M2") ,5), etc

    Use a value based on the number of significant partial pennies you are interested in...

    Steve

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Tom, Thanks, but that does not seem to work for me here.

    Steve, the cells are formatted to number with 2 decimals. I only want to see the error if 0.00 is not returned. So would it be ???

    round(Worksheets("Summary").Range("M2") ,2)

    ?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Why don't you try it? That should take less time than waiting for a reply...

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Fair comment, and I had, but wanted to be certain.

    round(Worksheets("Summary").Range("M2") ,2) <> 0

    seems to work for me. Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •