PDA

View Full Version : Urgent help in VBA for averaging data



xingmao
10-10-2007, 09:22 AM
Hi guys,

I got a problem. I got a 10156 data points in Column A, starting from Row 2 and I need to find the average of every set of 10 data points. However, there will be a problem with the last 6 data points.

I want to input the averaged values in Column E starting from Row 2.

I need to write a VBA about it.

Pls help.

Thanks!!!

mdmackillop
10-10-2007, 09:35 AM
Hi xingmao
Welcome to VBAX
Try
Sub AVs()
Dim LR As Long, i As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LR Step 10
Cells(i, 5).FormulaR1C1 = "=average(RC[-4]:R[9]C[-4])"
Next
End Sub

xingmao
10-10-2007, 03:09 PM
i got the error of 'Application defined error'

mdmackillop
10-10-2007, 03:13 PM
I don't see why. Here's a sample workbook.

xingmao
10-10-2007, 03:21 PM
i got it now. But the problem is that the values that are placed in column E are far apart. How to make the values follow each other continuously without any spaces in between

mdmackillop
10-10-2007, 03:36 PM
Sub AVs()
Dim LR As Long, i As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LR / 10
Cells(i + 1, 5).FormulaR1C1 = "=average(R[" & (i - 1) * 9 & "]C[-4]:R[" & i * 9 & "]C[-4])"
Next
End Sub

xingmao
10-10-2007, 11:17 PM
how is it that when i key in the macro, there is a compile error and the highlighted area is the C[-4]:R[ ?

mdmackillop
10-10-2007, 11:32 PM
Are you inserting spaces around the & operator? This is required.

xingmao
10-11-2007, 04:28 PM
thanks i got it. Do you know how to describe the macro? I need to write it out in words but i do not understand the words like Dim, Long, xlUp and the operator &. How come you need to put the operator so that the results will not have spaces?

mdmackillop
10-11-2007, 09:51 PM
Put the cursor over a word you don't understand and press F1 for Help.. That's what it is there for.
You need spaces round & so you don't get syntax errors. Just accept it.