Selecting a group of cells up in a range

Question

I have a For Next Loop that take each cell in a range, selects say 10 below it, performs a calculation on those ten cells and puts the results in the next column. It would for example take cell A1, sum A1-A10 and put the results in B1. It then loops to A2, sums A2- A11 and puts the results in B2. The following code (only excerpt) works fine.

I want to sum UP rather than down. Select cell A20, sum up A20-A10, and put the results in B20. Haven't been able to do it. In the following code the value for E1 would be 10 and is the number of cells selected down from each cell in range.

For Each Rng In Range("LogRd1")
    Rng.Select
    Selection.Resize((Range("E1").Value), 1).Select
    Rng.Offset(, 1).Formula = ((Application.WorksheetFunction.Sum(Selection)) / ((Range          ("E1").Value + 0.0001) - (Application.WorksheetFunction.Sum(Selection))))

Next Rng

Any help appreciated. Thanks, Grant

Tim Williams Tim Williams · Accepted Answer · 2014-04-23T22:33:01

EDITED AGAIN: a little more robust

Dim BLOCK_SIZE as Long
Dim Rng As Range, r as Range, s As Variant

BLOCK_SIZE = Range("E1").Value 

For Each Rng In Range("LogRd1")
If Rng.Row >= BLOCK_SIZE Then
    Set r = Rng.Offset(-(BLOCK_SIZE-1), 0).Resize(BLOCK_SIZE, 1)
    s = Application.Sum(r) 
    If Not IsError(s) Then
        Rng.Offset(0, 1).Value = s / ((BLOCK_SIZE + 0.0001) - s)
    Else
        Rng.Offset(0, 1).Value = "Sum Error!"
    End If
End If 'can offset to at least BLOCK_SIZE above this cell
Next Rng

Selecting a group of cells up in a range

1 Answers