incrementing cells withina formula in VB
 

Hi,


Do While Range("b11").Value 0
Range("e15").Value = Range("e15").Value + Range("c15").Value
Range("e16").Value = Range("e16").Value + Range("c16").Value
Range("e17").Value = Range("e17").Value + Range("c17").Value
.........
Range("e40").Value = Range("e40").Value + Range("c40").Value
Range("b11").Value = Range("b11").Value - 1
End

Loop

Any help appreciated. I'm trying to get a spreadsheet to check a list
of values if a condition is met. For example, B11 = 40 in this
instance. While this works as is, i would like to clean it up by
perhaps having a single line formula that would increment the row
values as it goes through the loop. along the lines of:

Range("b12").Value = "55"
Do While Range("b11").Value 0
Range("b12").Value = Range("b12").Value - Range("b11").Value
Range("e[B11]").Value = Range("e[B11]").Value +
Range("c[B11]").").Value
Range("b11").Value = Range("b11").Value - 1
End

Loop
Thanks

Paul

incrementing cells withina formula in VB
 

Hi Paul
not compelte sure but try
sub foo()
dim row_index as long
dim lastrow as long
with activesheet
lastrow=.range("B11").value
for row_index = lastrow to 1 step -1
.cells(row_index,"E").value=.cells(row_index,"E"). value + _
.cells(row_index,"C").value
next
end with
end sub



--
Regards
Frank Kabel
Frankfurt, Germany


Paul wrote:
Hi,


Do While Range("b11").Value 0
Range("e15").Value = Range("e15").Value + Range("c15").Value
Range("e16").Value = Range("e16").Value + Range("c16").Value
Range("e17").Value = Range("e17").Value + Range("c17").Value
........
Range("e40").Value = Range("e40").Value + Range("c40").Value
Range("b11").Value = Range("b11").Value - 1
End

Loop

Any help appreciated. I'm trying to get a spreadsheet to check a list
of values if a condition is met. For example, B11 = 40 in this
instance. While this works as is, i would like to clean it up by
perhaps having a single line formula that would increment the row
values as it goes through the loop. along the lines of:

Range("b12").Value = "55"
Do While Range("b11").Value 0
Range("b12").Value = Range("b12").Value - Range("b11").Value
Range("e[B11]").Value = Range("e[B11]").Value +
Range("c[B11]").").Value
Range("b11").Value = Range("b11").Value - 1
End

Loop
Thanks

Paul