Loop a formula in excel VBA

Posted by CEMG on Stack Overflow See other posts from Stack Overflow or by CEMG
Published on 2010-04-24T01:49:38Z Indexed on 2010/04/24 1:53 UTC
Read the original article Hit count: 295

Filed under:

I am trying to loop a formula in Column "D" until Column "B" doesn't have any more data.

The formula I am adding to Column "D" is : IF(ISNUMBER(C5),C5,IF(C5A5/3+OFFSET(C5,-1,0)) ,IF(C5<>C6,((OFFSET(C5,1,0)-OFFSET(C5,-2,0))(A5/3)+OFFSET(C5,-2,0)),"")))

So the result I want in Column "D" once the macro is run is this:

A B C D 3 May-10 78.0000 78.00000 1 Jun-10 52.06667 2 Jul-10 26.13333 3 Aug-10 0.2000 0.20000 1 Sep-10 0.21393 2 Oct-10 0.22786 3 Nov-10 0.2418 0.24179 1 Dec-10 0.26640 2 Jan-11 0.29102 3 Feb-11 0.3156 0.31563 1 Mar-11 0.34821 2 Apr-11 0.38080 3 May-11 0.4134 0.41338 1 Jun-11 0.44992 2 Jul-11 0.48646 3 Aug-11 0.5230 0.52300 1 Sep-11 0.56440 2 Oct-11 0.60580 3 Nov-11 0.6472 0.64720 1 Dec-11 0.43147

If someone can help me at what I am doing wrong with the VBA codes I would greatly appreciated. My CODES are the following:

Sub IsNumeric()

// first logic: IF(ISNUMBER(C6),C6 //

If Application.IsNumber(Range("c5").Value) Then

Range("d5").Value = Range("C5").Value

// second logic: IF(C6

ElseIf Range("c6").Value < Range("c5").Value Then

Range("d6").Value = Range("c6").Offset(2, 0).Value - Range("c6").Offset(-1, 0).Value * (Range("a6").Value / 3) + Range("c6").Offset(-1, 0).Value

// third logic: IF(C6<>C7,((OFFSET(C6,1,0)-OFFSET(C6,-2,0))*(A6/3)+OFFSET(C6,-2,0)),""))) //

ElseIf Range("c6").Value <> Range("c7").Value Then

Range("d6").Value = (Range("c6").Offset(1, 0).Select) - Range("c6").Offset(-2, 0).Select * (Range("a6").Select / 3) + Range("c6").Offset(-2, 0).Select

Else Range("d6").Value = ""

End If

End Sub

© Stack Overflow or respective owner

Related posts about loops