Overflow error

[totalnatal]

Hello,

I ahve the following code.

Basically it selects data at a certain date, then moves down selecting the data at each line and calculating a weighted average (w) out of it. If that average is less than a certain value located in another cell then it puts a Yes in another cell.

For some reason when it reached the w it gives me an overflow error everytime and i have no clue how to solve it.

Could you help me out ?

Thanks

Dim rng, rng1, rng2, rng3, rng4, rng5, rng6, rng7, rng8, rng9, rng10, rng14, rng15, rng16, rng17 As Range
Dim f As Double, m As Double, e As Date, i As Double, s As Double, a As Double, b As Double, c As Double, d As Double, aj As Double, g As Double, al As Double, j As Double, k As Date, l As Double, n As Double, o As Double, p As Double, q As Double, r As Double, am As Double, t As Double, u As Double, v As Date, y As Double, z As Double, ab As Double, ac As Double, ad As Double, ae As Double, af As Double, ag As Double, ai As Double, ak As Double, ax As Double, av As Double, az As Double
Dim weight1, weight2, weight3, weight4, weight5, weight6, weight7, weight8, weight9, weight10 As Long
Dim w As Double

f = Range("f1").Value * 12
    Set rng = Range("I3:I5413")
    m = Application.Max(rng)
    e = Edate(m, -12 * Range("f1").Value)

    i = 0
    s = 0

    Do

    ai = Edate(Range("i3").Offset(s, 0).Value, f)
    x = Range("i3").Offset(s, 1).Value
    a = Range("i3").Offset(s, 2).Value
    b = Range("i3").Offset(s, 3).Value
    c = Range("i3").Offset(s, 4).Value
    d = Range("i3").Offset(s, 5).Value
    aj = Range("i3").Offset(s, 6).Value
    ax = Range("i3").Offset(s, 7).Value
    g = Range("i3").Offset(s, 8).Value
    h = Range("i3").Offset(s, 9).Value
    al = Range("i3").Offset(s, 10).Value
    k = Range("i3").Offset(s, 0).Value
    weight1 = Cells(4, 3).Value
    weight2 = Cells(5, 3).Value
    weight3 = Cells(6, 3).Value
    weight4 = Cells(7, 3).Value
    weight5 = Cells(8, 3).Value
    weight6 = Cells(9, 3).Value
    weight7 = Cells(10, 3).Value
    weight8 = Cells(11, 3).Value
    weight9 = Cells(12, 3).Value
    weight10 = Cells(13, 3).Value
    
    
        Do

        l = Range("i3").Offset(i, 1).Value
        av = Range("i3").Offset(i, 2).Value
        n = Range("i3").Offset(i, 3).Value
        o = Range("i3").Offset(i, 4).Value
        p = Range("i3").Offset(i, 5).Value
        q = Range("i3").Offset(i, 6).Value
        r = Range("i3").Offset(i, 7).Value
        am = Range("i3").Offset(i, 8).Value
        t = Range("i3").Offset(i, 9).Value
        u = Range("i3").Offset(i, 10).Value
        v = Range("i3").Offset(i, 0).Value
    
        w = (1 + l / x * weight1 + av / a * weight2 + n / b * weight3 + o / c * weight4 + p / d * weight5 + q / aj * weight6 + r / ax * weight7 + am / g * weight8 + t / h * weight9 + u / al * weight10)
        
        If w < Range("f8").Value Then
        Range("an3").Offset(s, 1).Value = "Yes"
        End If

        i = i + 1

        Loop Until v > ai

    s = s + 1
    i = s

    Loop Until k = ai

 

[totalnatal]

Note that some of the data cells might have a formula in them but empty(0).
don't know if that could be the problem

 

[VoG]

Check the way you have declared your variables.

Dim weight1, weight2, weight3, weight4, weight5, weight6, weight7, weight8, weight9, weight10 As Long

declares weight10 as long and all the preceding ones as Variant.

 

[totalnatal]

Hey,

It still comes up with the overflow error. I have individually defined every variable.

 

[VoG]

Maybe w should be declared as Double.

 

[totalnatal]

It already is.

 

[VoG]

Oops! Anyway, maybe try evaluating each bit of that formula separately to see which bit is generating the error. I don't think that blank cells are the problem - they would generate a division by zero error.

 

[totalnatal]

Ok I onyl have data in the first column : Range("i3").Offset(i,1) which has got data in.

As soon as the code hits the second column it comes up as overflow-->problem comes from dividing zero by zero.

Any idea as to how to empty a cell of its result AND formula ? So far I have used clearcontents but the formulas remain for some reason.

 

[totalnatal]

There's actually no zeros from the second column onwards, it's just empty cells being divided by empty cells.

 

[VoG]

So you need to include a test for the divisors' values. If any is zero set w to 0 or -999 or whatever.