Loop a formula in excel VBA
- by CEMG
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