Mavis Beacon
03-07-2013, 12:38 AM
Hello,
I have excel 2007. I'm trying to get the average of a column of numbers. I can't use exact cell addresses as I've used a loop that does this for every column in a worksheet. There are a different number of rows in every column and a different number of columns in every worksheet. The line that I'm having trouble with is
ActiveCell.FormulaArray = [Average(Range(Cells(ActiveCell.Offset(-40, 0).End(xlDown).Row, ActiveCell.Column)))]
When I run it, I keep getting the error message #VALUE!.
What I'm trying to get the code to do is to 1) loop through columns of data in a worksheet, 2) insert a blank column next to each one, 3) copy over data from the previous column if the value of the cell is <2, 4) get the mean and STDEV of the new column.
Could anyone tell me where I'm going wrong?
Many Thanks.
My full code is:
Sub TEST()
'
' TEST Macro
'
' Keyboard Shortcut: Ctrl+o
'
Cells(3, 1).Activate
Dim Lastcol As Integer, c As Integer
Lastcol = ActiveSheet.UsedRange.Columns.Count
For c = 1 To Lastcol
If Cells(3, c) <> vbNullString Then
Dim StartPoint As Range
Set StartPoint = ActiveCell
StartPoint.Offset(0, 1).EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
StartPoint.Offset(0, 1).Select
Do
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>2,"""",RC[-1])"
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(0, -1) = vbNullString
StartPoint.Offset(0, 1).EntireColumn.Select
Selection.NumberFormat = "0.0000"
StartPoint.Offset(40, 1).Select
ActiveCell.FormulaArray = [Average(Range(Cells(ActiveCell.Offset(-40, 0).End(xlDown).Row, ActiveCell.Column)))]
StartPoint.Offset(0, 2).Select
End If
Next c
End Sub
I have excel 2007. I'm trying to get the average of a column of numbers. I can't use exact cell addresses as I've used a loop that does this for every column in a worksheet. There are a different number of rows in every column and a different number of columns in every worksheet. The line that I'm having trouble with is
ActiveCell.FormulaArray = [Average(Range(Cells(ActiveCell.Offset(-40, 0).End(xlDown).Row, ActiveCell.Column)))]
When I run it, I keep getting the error message #VALUE!.
What I'm trying to get the code to do is to 1) loop through columns of data in a worksheet, 2) insert a blank column next to each one, 3) copy over data from the previous column if the value of the cell is <2, 4) get the mean and STDEV of the new column.
Could anyone tell me where I'm going wrong?
Many Thanks.
My full code is:
Sub TEST()
'
' TEST Macro
'
' Keyboard Shortcut: Ctrl+o
'
Cells(3, 1).Activate
Dim Lastcol As Integer, c As Integer
Lastcol = ActiveSheet.UsedRange.Columns.Count
For c = 1 To Lastcol
If Cells(3, c) <> vbNullString Then
Dim StartPoint As Range
Set StartPoint = ActiveCell
StartPoint.Offset(0, 1).EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
StartPoint.Offset(0, 1).Select
Do
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>2,"""",RC[-1])"
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(0, -1) = vbNullString
StartPoint.Offset(0, 1).EntireColumn.Select
Selection.NumberFormat = "0.0000"
StartPoint.Offset(40, 1).Select
ActiveCell.FormulaArray = [Average(Range(Cells(ActiveCell.Offset(-40, 0).End(xlDown).Row, ActiveCell.Column)))]
StartPoint.Offset(0, 2).Select
End If
Next c
End Sub