Averaging A Named Range

[MrJRussell]

I've read quite a lot on this topic but still can't get my code to work...

I've got a sheet that contains student test scores. Its sorted by grade level and I want to average all the scores over each grade. My macro moves down the grade level column and inserts blank rows to separate all the students in each grade into chunks. I then define a range in the score column that goes from the first to the last record in a particular grade. I then want to insert the average in the blank row below the defined range.

Here is my code:

<code>
Sub FindTheRITAverages()


Dim firstnumber As Integer
Dim secondnumber As Integer
Dim counter As Integer
Dim rangeTop As Integer
Dim Avg As Integer
Dim myRange As Range


counter = 2
rangeTop = 2


Call FindTheBottom(8)
Do Until counter = lastrow
firstnumber = Cells(counter, 8).Value
counter = counter + 1
secondnumber = Cells(counter, 8).Value
If secondnumber <> firstnumber Then
Rows(counter).Select
Selection.Insert Shift:=xlDown
Set myRange = Range(Cells(rangeTop, 10), Cells(counter, 10))
Avg = Applictaion.WorksheetFunction.Average(myRange)
counter = counter + 1
Else
counter = counter + 1
End If
Loop


End Sub
</code>

I get an "Object Required" error. I've also tried to write the =Average() function into the sheet to find the average that way, but I also got an error. Thanks for any help you might provide.

 

[JoeMo]

Hard to sort your problem with no knowledge of your data layout or the called subroutine. If you post a representative sample of your data that can be copied and pasted into Excel, and a description of what you want to achieve, someone here can give you a working VBA solution.

 

[MrJRussell]

Hard to sort your problem with no knowledge of your data layout or the called subroutine. If you post a representative sample of your data that can be copied and pasted into Excel, and a description of what you want to achieve, someone here can give you a working VBA solution.

Thanks for your quick response.

The data layout looks something like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Grade Level (Column H)[/TD]
[TD]Test (Column I)[/TD]
[TD]Score (Column J)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Reading[/TD]
[TD]121[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Math[/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Science[/TD]
[TD]114[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Reading[/TD]
[TD]115[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Math[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Reading[/TD]
[TD]133[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Math[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Science[/TD]
[TD]145[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Reading[/TD]
[TD]121[/TD]
[/TR]
</tbody>[/TABLE]

The subroutine is just to find the row number of the last record, so I can exit the Do loop.

Public lastrow As Integer


Sub FindTheBottom(columnnumber)
Dim basement As Long
Cells(1, columnnumber).Select
Do Until basement = 1048576
Selection.End(xlDown).Select
basement = ActiveCell.Row
Loop
Selection.End(xlUp).Select
lastrow = ActiveCell.Row
'MsgBox (lastrow)
End Sub

The error is thrown here: Avg = Applictaion.WorksheetFunction.Average(myRange)