Overflow where there should not be one ( no divide by zero)

Philosophaie · May 6th 2013

I keep getting an "overflow" error on t1.
There is a number in column F or JD from 2450000.5 to 2456666.5 in every one of the cells and each successive cell only differs by only 10 or so.
No cell is empty throughout the range.

Code

Dim t1 As Double, t2 As Double, JD(100) As Double
        maxn = Cells(Rows.Count, "F").End(xlUp).Row - 2
        For k = 1 To maxn
                  'checks for empty cells
                  If JD(k) = "" Or JD(k + 1) = "" Or JD(k + 2) = "" Then
                            MsgBox ("JD=nothing")
                            Exit Sub
                  End If
                  t1 = 24 * 3600 * (JD(k) - JD(k + 1))
                  t2 = 24 * 3600 * (JD(k + 2) - JD(k + 1))
         Next k

Display More

GCExcel · May 6th 2013

Re: Overflow where there should not be one ( no divide by zero)

That's a weird problem... I've found this post about a similar issue :
http://www.mrexcel.com/forum/e…-hhh-%3D-24-*-3600-a.html

This seems like a good workaround :

Code

Dim t1 As Double, t2 As Double, JD(100) As Double
Dim k As Long, maxn As Long




Dim d24 As Double, d3600 As Double
d24 = 24
d3600 = 3600








maxn = Cells(Rows.Count, "J").End(xlUp).Row - 2
For k = 1 To maxn
     'checks for empty cells
    If JD(k) = "" Or JD(k + 1) = "" Or JD(k + 2) = "" Then
        MsgBox ("JD=nothing")
        Exit Sub
    End If
    t1 = d24 * d3600 * (JD(k) - JD(k + 1))
    t2 = d24 * d3600 * (JD(k + 2) - JD(k + 1))
Next k

Display More

Regards.

Gary's Student · May 6th 2013

Re: Overflow where there should not be one ( no divide by zero)

You need to trap the error to see what is going on. Use an On Error Goto to a block of code that MsgBoxes the values of k, JD(k) and JD(k+1).

Philosophaie · May 6th 2013

Re: Overflow where there should not be one ( no divide by zero)

Thanx that fixed it!!!

Overflow where there should not be one ( no divide by zero)

Participate now!

Share