# Thread: Overflow Error (XL97:SR2)

1. ## 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. ## 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. ## 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. ## Re: Overflow Error (XL97:SR2)

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

5. ## 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. ## Re: Overflow Error (XL97:SR2)

I will provide sample code shortly.

7. ## 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. ## Re: Overflow Error (XL97:SR2)

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

Tony.

9. ## 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. ## 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. ## 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
•