copy formula with adding e.g. 10 cells
 

Hello,

I've got a data sheet with 1000 values (A1:J100). I would like to calculate
the average of every 10th value in the "A" colomn. This will be the average
A1:J1; A10:J10;A20:J20 etc I want to transport these average values to sheet
2. Is there anyone who can help me with this case? I want to prevent to make
a formula 100 times by doing this manual.

René

copy formula with adding e.g. 10 cells
 

Hi René,

You can use the code below to get your results. Add the code to a module in
your workbook. Make sure that you have worksheets named "Sheet1" and
"Sheet2". This will return the AVERAGE of the 10th rows to Sheet2.

Hope that helps.

Regards,
James

Sub CalculateAverages()
Dim wkb As Workbook
Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim rng As Range
Dim i As Integer

' Set a reference to the current Workbook.
Set wkb = ThisWorkbook
' Set a reference to Sheet1.
Set wks1 = wkb.Worksheets("Sheet1")
' Set a reference to Sheet2.
Set wks2 = wkb.Worksheets("Sheet2")
' Set a Cell starting point in Worksheet 2 for where the
' results will be placed.
Set rng = wks2.Range("A1")

For i = 0 To 10
If i = 0 Then
' In the first loop i = 0, but the Row reference needs to be
' set to "1".
rng.Formula = "=AVERAGE(Sheet1!A1:J1)"
Else
' All other Rows can be found by multiplying "i" by 10.
rng.Formula = "=AVERAGE(Sheet1!A" & i * 10 & ":J" & i * 10 & ")"
End If
' Move to the next Cell down in Worksheet 2.
Set rng = rng.Offset(1, 0)
Next i

Set wkb = Nothing
Set wks1 = Nothing
Set wks2 = Nothing
Set rng = Nothing

MsgBox "DONE"
End Sub

"René" wrote:

Hello,

I've got a data sheet with 1000 values (A1:J100). I would like to calculate
the average of every 10th value in the "A" colomn. This will be the average
A1:J1; A10:J10;A20:J20 etc I want to transport these average values to sheet
2. Is there anyone who can help me with this case? I want to prevent to make
a formula 100 times by doing this manual.

René

copy formula with adding e.g. 10 cells
 

Thanks a lot!!!!!!

"JS2004R6" wrote:

Hi René,

You can use the code below to get your results. Add the code to a module in
your workbook. Make sure that you have worksheets named "Sheet1" and
"Sheet2". This will return the AVERAGE of the 10th rows to Sheet2.

Hope that helps.

Regards,
James

Sub CalculateAverages()
Dim wkb As Workbook
Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim rng As Range
Dim i As Integer

' Set a reference to the current Workbook.
Set wkb = ThisWorkbook
' Set a reference to Sheet1.
Set wks1 = wkb.Worksheets("Sheet1")
' Set a reference to Sheet2.
Set wks2 = wkb.Worksheets("Sheet2")
' Set a Cell starting point in Worksheet 2 for where the
' results will be placed.
Set rng = wks2.Range("A1")

For i = 0 To 10
If i = 0 Then
' In the first loop i = 0, but the Row reference needs to be
' set to "1".
rng.Formula = "=AVERAGE(Sheet1!A1:J1)"
Else
' All other Rows can be found by multiplying "i" by 10.
rng.Formula = "=AVERAGE(Sheet1!A" & i * 10 & ":J" & i * 10 & ")"
End If
' Move to the next Cell down in Worksheet 2.
Set rng = rng.Offset(1, 0)
Next i

Set wkb = Nothing
Set wks1 = Nothing
Set wks2 = Nothing
Set rng = Nothing

MsgBox "DONE"
End Sub

"René" wrote:

Hello,

I've got a data sheet with 1000 values (A1:J100). I would like to calculate
the average of every 10th value in the "A" colomn. This will be the average
A1:J1; A10:J10;A20:J20 etc I want to transport these average values to sheet
2. Is there anyone who can help me with this case? I want to prevent to make
a formula 100 times by doing this manual.

René

copy formula with adding e.g. 10 cells
 

James, it works!!! Once again thanks!

"René" wrote:

Hello,

I've got a data sheet with 1000 values (A1:J100). I would like to calculate
the average of every 10th value in the "A" colomn. This will be the average
A1:J1; A10:J10;A20:J20 etc I want to transport these average values to sheet
2. Is there anyone who can help me with this case? I want to prevent to make
a formula 100 times by doing this manual.

René