Sum two rows in a range

[GIS2013]

Hi. I have a "big" range and a smaller one in which each cell is the sum of two cells in the big range. It's more simple in the file attached. I was thinking about something like this, any modification/other suggestion?

Private Sub test()
Dim a As Range, b As Range, c As Byte
    With Worksheets("s")
    Set a = .Range("a2:e6")
        For Each b In a
        b.Value = Application.WorksheetFunction.Sum(.Cells(b.Offset(9), b.Offset(11)))
        Next b
    End With
End Sub

[HaHoBe]

Hi, v,

you should look up the VBA help for Cells which requires the Row number first and either a numeric value or alphabetic string for the Column.

You would need to adapt the sheet name as the name in the attachment differs from s:

Private Sub test()
Dim rngCell As Range
For Each rngCell In Worksheets("s").Range("A2:E6")
  With rngCell
    .Value = WorksheetFunction.Sum(.Offset(9), .Offset(11))
  End With
Next rngCell
End Sub

Ciao,
Holger

[GIS2013]

Hi Holger,
Thank you, I'll read again about CELLS.
I could better exmplain myself from the first place: the "big" range I was relating to is on a monthly basis, and what I'm trying to do is to create a new range which will be based on the "big" range but be on a quarter basis.
For example if A10 = X, A11 = Y and A12=Z then A1, which represents a quarter, will be equal to X+Y+Z.
Hope it's more clear now.

[HaHoBe]

Hi, GIS2013,

could you please make clear if you want to add two rows like indicated in the title or 3 rows like your last post which would make up a quarter? In the latter part for the second row it should be 13 to 15?

Private Sub test()
Dim rngCell As Range
For Each rngCell In Worksheets("1").Range("A2:E6")
  With rngCell
    .Value = WorksheetFunction.Sum(.Offset(8 + (.Row - 2) * 2).Resize(3, 1))
  End With
Next rngCell
End Sub

Ciao,
Holger

[GIS2013]

I'm sorry for the confusion. Yes - quarter, 13:15, and in the third row 16:18 etc. I've just run your code and it works smoothly. I'll also take time to learn about RESIZE. Thanks Holger