Newbie questions

[pped]

Hi Everyone,

I'm new here and new to programming in VBA. I have a question about this run- time error 6. Please forgive the ugly code, I am just learning, so if there are any suggestions on cleaning the code and finding the error, I would be really excited!

 Sub Axxten()
      
      Dim x As Double
      Dim v As Double
      Dim i As Integer
      Dim k As Integer
      Dim col As Integer
      Dim pow As Integer
     
     col = 14
     v = 1 / (1 + Cells(41, 1))
      
      'First cell in appropriate column pivoting downward
      For i = 1 To 35

        pow = 1
        x = 0
        k = i
        
      
             ' Set Do loop to stop when an empty cell is reached
             Do Until IsEmpty(Cells(1 + k, 2))
         
         
                    'Attempt to stop run-time error 6
                    If IsEmpty(Cells(1 + k + 10, 2)) Then Cells(1 + k + 10, 2) = 0
         
             'Caclulate adouble dot
              x = x + (Cells(1 + k, 2) / Cells(1 + i, 2)) * (Cells(1 + k + 10, 2) / Cells(1 + i + 10, 2)) * v ^ (pow)
             
                    'Fill in zeros for appropriate cells
                    If x = 0 Then Cells(1 + i, col) = x
                    
             'Out another year, raise to another power
             pow = pow + 1
             
             ''Out another year, next survivorship
             k = k + 1
            
             Loop
          
      'Place adouble dot total under qs
      Cells(1 + i, col) = 1 - (1 - v) * x
    
    'Pivot downward
    Next i
   End Sub

 

[Simon Lloyd]

Your runtime error 6 is an overflow error, it will be because this calculation

x = x + (Cells(1 + k, 2) / Cells(1 + i, 2)) * (Cells(1 + k + 10, 2) / Cells(1 + i + 10, 2)) * v ^ (pow)

pretty much amounts to 0+1x0/0x1x1 as you can see its nuts

 

[pped]

Your runtime error 6 is an overflow error, it will be because this calculationpretty much amounts to 0+1x0/0x1x1 as you can see its nuts

Thanks a ton! Good eye! As you can see I am getting a little cross-eyed with these calculations...

Is there anyway I can escape the Do Until....Loop by writing?

 If IsEmpty(Cells(1 + i + 10, 2)) Then Cells(1 + i, col)  = 0  Exit Do

Right now the code isn't working. What I would like to do is fill in "0" for all places in the calc where IsEmpty(Cells(1 + i + 10, 2)) since the calculation doesn't make sense there.

 

[Simon Lloyd]

Try

If IsEmpty(Cells(1 + i + 10, 2)) Then Cells(1 + i, col)  = 0  Goto Nxt

and then on the next line right after LOOP put Nxt: (that's including the

 

[pped]

Try

If IsEmpty(Cells(1 + i + 10, 2)) Then Cells(1 + i, col)  = 0  Goto Nxt

and then on the next line right after LOOP put Nxt: (that's including the

Ok, I think the smiley face is throwing an error...

Seriously though, now I am getting a compiler error saying "end of statement expected with the above code.

 

[pped]

Ok got it, phew. Thanks for your patience