Help to modify VBA code to display result over 12 cells

[mdavid800]

Hi there

Someone on this forum helped me with a VBA code to get the average of every 12 results in a column.

sub avg_1()
Dim i As Long
For i = 35 To 8962 Step 12
 Range("f" & Rows.Count).End(xlUp).Offset(1).Value = WorksheetFunction.Average(Range("C" & i).Resize(12))
Next i
End Sub

This works great but I was wondering if someone could help me modify it so that the instead of the results being displayed in one cell they are displayed over 12 cells.

For instance this code current calculates the average of every 12 cells starting at Cell C35 (i.e. the average of cell C35 to C46 and then the average of Cell C47 to Cell C58 and so on) it then displays this in cell f1,f2 etc . What I would really like to do is display this average of the cells in column C over the corresponding cells in column F so the average of cells C35 to C46 would be shown in cells F35 to F46 and the average of cells C47 to C58 would be shown in cells F47 to F46 and so on.

I hope that makes sense , it would really help with the other calculations I am using.

Thank you in advance

David

 

[cicconmr]

So rather than the average simply being in cell F46 for the average of C35:C46, you'd rather have it show up in F35:F46?

That's pretty easy to do by just altering what you have, where it says:

Range("f" & Rows.Count).End(xlUp).Offset(1) replace it with Range("f" & i) only.

See if that works, I'm not 100% on it....

 

[mdavid800]

Hi cicconmr

this does part of what i want to do it puts it in the first cell i would like for that same number to copy down into all 12 cells

any ideas?

 

[cicconmr]

try this instead....Range("f" & i:"f" & i+11)

This or i + 12 should do it, not 100% sure not running the code over here

 

[mdavid800]

When i enter this i get "compile error - expected :list separator or ) with the colon highlighted )

i appreciate the help

 

[cicconmr]

Try playing around with it....For instance this may be the way to do this:

Range("f"i:i+11) ??