Please Help Excel vba Loop question

I can't seem to figure out the better way to code a loop.
Please help Thank you, JB

Long n short of it, code I'm using is below:
from row 3 and below (col a is input area): b=a *(row1 coL b), c=b*(row1 coL c), d=c*(row1 coL d), e=d*(row1 coL e)

amount of Columns are fixed
amount of Rows are variable

a b c d e
1 . .03 .00 .03 .07
2 . 2008 2009 2010 2011 2012

3 . 1 1.03 1.03 1.06 1.14
4 . 3 3.09 3.09 3.18 3.41
5 . 2 2.06 2.06 2.12 2.27
6 . 5 5.15 5.15 5.30 5.68
so on....
Total 11.0 11.3 11.7 12.5

I activate the following by a 'Worksheet Change' in input column.

Sub calculationMFI()
Dim ciL As Range
Dim lr0 As Long
Dim lc1 As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
lr0 = 0
lc1 = 1
For Each ciL In Sheet1.Range("H5:AA5")
ciL.Offset(lr0, lc1).Value = ciL.Offset(lr0, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0, 0)
ciL.Offset(lr0 + 1, lc1).Value = ciL.Offset(lr0 + 1, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 1, 0)
ciL.Offset(lr0 + 2, lc1).Value = ciL.Offset(lr0 + 2, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 2, 0)
ciL.Offset(lr0 + 3, lc1).Value = ciL.Offset(lr0 + 3, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 3, 0)
ciL.Offset(lr0 + 4, lc1).Value = ciL.Offset(lr0 + 4, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 4, 0)
ciL.Offset(lr0 + 5, lc1).Value = ciL.Offset(lr0 + 5, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 5, 0)
ciL.Offset(lr0 + 6, lc1).Value = ciL.Offset(lr0 + 6, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 6, 0)
ciL.Offset(lr0 + 7, lc1).Value = ciL.Offset(lr0 + 7, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 7, 0)
ciL.Offset(lr0 + 8, lc1).Value = ciL.Offset(lr0 + 8, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 8, 0)
ciL.Offset(lr0 + 9, lc1).Value = ciL.Offset(lr0 + 9, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 9, 0)
ciL.Offset(lr0 + 10, lc1).Value = ciL.Offset(lr0 + 10, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 10, 0)
ciL.Offset(lr0 + 11, lc1).Value = ciL.Offset(lr0 + 11, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 11, 0)
ciL.Offset(lr0 + 12, lc1).Value = ciL.Offset(lr0 + 12, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 12, 0)
ciL.Offset(lr0 + 13, lc1).Value = ciL.Offset(lr0 + 13, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 13, 0)
ciL.Offset(lr0 + 14, lc1).Value = ciL.Offset(lr0 + 14, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 14, 0)
ciL.Offset(lr0 + 15, lc1).Value = ciL.Offset(lr0 + 15, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 15, 0)
ciL.Offset(lr0 + 16, lc1).Value = ciL.Offset(lr0 + 16, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 16, 0)
ciL.Offset(lr0 + 17, lc1).Value = ciL.Offset(lr0 + 17, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 17, 0)
ciL.Offset(lr0 + 18, lc1).Value = ciL.Offset(lr0 + 18, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 18, 0)
ciL.Offset(lr0 + 19, lc1).Value = ciL.Offset(lr0 + 19, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 19, 0)
ciL.Offset(lr0 + 20, lc1).Value = ciL.Offset(lr0 + 20, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 20, 0)
ciL.Offset(lr0 + 21, lc1).Value = ciL.Offset(lr0 + 21, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 21, 0) ciL.Offset(lr0 + 22, lc1).Value = ciL.Offset(lr0 + 22, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 22, 0) ciL.Offset(lr0 + 23, lc1).Value = ciL.Offset(lr0 + 23, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 23, 0)
ciL.Offset(lr0 + 24, lc1).Value = ciL.Offset(lr0 + 24, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 24, 0)
ciL.Offset(lr0 + 25, lc1).Value = ciL.Offset(lr0 + 25, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 25, 0)
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories