Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Overflow Error (XL97:SR2)

    I receive an "Overflow" error message when I try to divide two amounts ie 0 / 0 via code. I've tried using "On Error Resume Next" but but then have an issue with Row3.

    My goal is to record the % increase. Stated in a formula (A1-B1)/B1
    <table border=1><td></td><td>A</td><td>B</td><td>C</td><td>row1</td><td>1000</td><td>750</td><td>33.33%</td></tr><td>row2</td><td>0</td><td>0</td><td>-</td></tr><td>row3</td><td>1000</td><td>0</td><td>100%</td>

    </table>
    Thanks for your assistance,
    John

    PS I miss the search feature. I'm sure I read a thread on this.

  2. #2
    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

    Re: Overflow Error (XL97:SR2)

    What do you want to have the value equal to if "B" = 0? What do you want it equal to if BOTH A=0 AND B = 0? What is the code you currently have?

    Steve

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Overflow Error (XL97:SR2)

    As divisiion by zero will always produce an error, you should test if the divisor is 0, and if so enter value of 0 or else carry out the division operation and use the result

    Your cell formula would be =IF(B1=0,0,(A1-B1)/B1).

    Andrew C

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Overflow Error (XL97:SR2)

    If Both A & B = 0 then the result should be 0

  5. #5
    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

    Re: Overflow Error (XL97:SR2)

    What about the other 2 questions I asked?

    Andrew responded with a formula, but I got the impression you were doing this with a macro since you got an overflow error and you used on error resume next. I need the code to help modify it (at least the "snippet of code" with the formula that causes it to "crash")

    Steve

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Overflow Error (XL97:SR2)

    I will provide sample code shortly.

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Overflow Error (XL97:SR2)

    A sample file is attached. The code reads:

    Sub PercentChange()
    Do Until IsEmpty(ActiveCell.Offset(0, 1))
    Cur = ActiveCell.Offset(0, -2)
    Prior = ActiveCell.Offset(0, -1)
    ActiveCell = (Cur - Prior) / Prior
    ActiveCell.Offset(1, 0).Select
    Loop
    End Sub

  8. #8
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Overflow Error (XL97:SR2)

    0/0 does not =0. It is indeterminate. (No finite answer)

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Overflow Error (XL97:SR2)

    Try the following :

    Sub PercentChange()
    Sub PercentChange()
    Do Until IsEmpty(ActiveCell.Offset(0, 1))
    Cur = ActiveCell.Offset(0, -2)
    Prior = ActiveCell.Offset(0, -1)
    If Prior <> 0 Then
    ActiveCell = (Cur - Prior) / Prior
    Else
    ActiveCell = 0
    End If
    ActiveCell.Offset(1, 0).Select
    Loop
    End Sub

    or possibly more efficient :

    Sub PercentChange2()
    Dim rngR As Range, c As Range
    Set rngR = Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, -1).End(xlDown)).Offset(0, 1)
    For Each c In rngR
    If c.Offset(0, -1) <> 0 Then
    c = (Val(c.Offset(0, -2)) - Val(c.Offset(0, -1))) / Val(c.Offset(0, -1))
    Else
    c = 0
    End If
    Next
    End Sub


    Andrew C

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Overflow Error (XL97:SR2)

    Andrew,

    I changed it slightly to:

    Sub PercentChange()
    Do Until IsEmpty(ActiveCell.Offset(0, 1))
    Cur = ActiveCell.Offset(0, -2)
    Prior = ActiveCell.Offset(0, -1)
    If Prior <> 0 Then
    ActiveCell = (Cur - Prior) / Prior * 100
    Else
    ActiveCell = 0
    If Cur > 0 Then
    ActiveCell = 100
    ElseIf Cur < 0 Then
    ActiveCell = -100
    End If
    End If
    ActiveCell.Offset(1, 0).Select
    Loop
    End Sub


    Many thanks,
    John

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Overflow Error (XL97:SR2)

    John,

    Use what you are most comfortable with, I just tried to eliminate the need to select cells before performing an operation on them.

    As regards the missing Serach facility, our overworked Admins are working hard on the problems and hopefully soon we will have business as usual.

    Andrew

Posting Permissions

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