Average function not working

[SuperNewToThis]

Hey everyone!

I have a bit of a problem with the average function. Essentially, I'm trying to calculate an average of a range and inputting it into a new column. The range spans a single row and multiple columns. I want an average for every row in the worksheet, and I need this to work for worksheets with a variety of number of rows.

Every time I run this macro, the error "Unable to get the Average property of the Worksheet function" (runtime error 1004) comes up. It actually does the average correctly, but will not continue to the next function. I've indicated where the problem lies in the VBA code.

Here's a sample of my data:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Grit1[/TD]
[TD]Grit2[/TD]
[TD]Grit3[/TD]
[TD]Grit4[/TD]
[TD]Grit5[/TD]
[TD]Grit6[/TD]
[TD]Grit7[/TD]
[TD]Grit8[/TD]
[/TR]
[TR]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]NA[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]2[/TD]
[TD]NA[/TD]
[/TR]
</tbody>[/TABLE]

Here's the code in question:

Sub GRITTEST3()'
' GRITTEST3 Macro
'


'
Dim Usdrws As Long
Dim Rng As Range
Dim Fnd As Range
Dim C As Range



Usdrws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


'Inputting total missing, total sum, total avg
Set Fnd = Cells.Find(What:="Grit8", After:=[A1], LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Fnd Is Nothing Then
MsgBox "Grit8 not found"
Exit Sub
End If
Fnd.Offset(0, 1).Resize(, 3).EntireColumn.Insert
Fnd.Offset(0, 2) = "Grit_Tot_Avg"
Fnd.Offset(0, 3) = "Grit_Tot_Sum"
Fnd.Offset(0, 1) = "Grit_Tot_Miss"
'Count missing data for grit variable
For Each Rng In Fnd.Offset(1, 1).Resize(Usdrws - Fnd.Row)
Rng.Value = Application.WorksheetFunction.CountIf(Rng.Offset(, -8).Resize(, 8), "NA")
Next Rng
'Averaging scores
For Each Rng In Fnd.Offset(2, 2).Resize(Usdrws - Fnd.Row)
If Rng.Offset(, -1) > 1 Then
Else
'RUNTIME ERROR OCCURS HERE
Rng.Value = Application.WorksheetFunction.Average(Rng.Offset(, -9).Resize(, 8))
End If
Next Rng
'Summing scores
For Each Rng In Fnd.Offset(2, 3).Resize(Usdrws - Fnd.Row)
If Rng.Offset(, -2) > 1 Then
Else
Rng.Value = Application.WorksheetFunction.Average(Rng.Offset(, -9).Resize(, 8)) * 7
End If
Next Rng
End Sub

I'd like to be able to use this macro in any worksheet with "Grit8" in it regardless of its position in the worksheet, which is why I do not refer to specific ranges in the sheet (i.e., A1:B1). If you guys need any other information, let me know!

Thanks!

 

[Scott T]

The error was happening when rng got to row 7 a empty row. This ran with out any errors for me

Sub GRITTEST3() '' GRITTEST3 Macro
'




'
Dim Usdrws As Long
Dim Rng As Range
Dim Fnd As Range
Dim C As Range






Usdrws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row




'Inputting total missing, total sum, total avg
Set Fnd = Cells.Find(What:="Grit8", After:=[A1], LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Fnd Is Nothing Then
MsgBox "Grit8 not found"
Exit Sub
End If
Fnd.Offset(0, 1).Resize(, 3).EntireColumn.Insert
Fnd.Offset(0, 2) = "Grit_Tot_Avg"
Fnd.Offset(0, 3) = "Grit_Tot_Sum"
Fnd.Offset(0, 1) = "Grit_Tot_Miss"
'Count missing data for grit variable
For Each Rng In Fnd.Offset(1, 1).Resize(Usdrws - Fnd.Row)
Rng.Value = Application.WorksheetFunction.CountIf(Rng.Offset(, -8).Resize(, 8), "NA")
Next Rng
'Averaging scores
For Each Rng In Fnd.Offset(2, 2).Resize(Usdrws - Fnd.Row)
If Rng.Offset(, -1) > 1 [COLOR=#ff0000]Or Rng.Offset(, -1) = ""[/COLOR] Then
Else
'RUNTIME ERROR OCCURS HERE
Rng.Value = Application.WorksheetFunction.Average(Rng.Offset(, -9).Resize(, 8))
End If
Next Rng
'Summing scores
For Each Rng In Fnd.Offset(2, 3).Resize(Usdrws - Fnd.Row)
If Rng.Offset(, -2) > 1 [COLOR=#ff0000]Or Rng.Offset(, -1) = ""[/COLOR] Then
Else
Rng.Value = Application.WorksheetFunction.Average(Rng.Offset(, -9).Resize(, 8)) * 7
End If
Next Rng
End Sub

 

[SuperNewToThis]

Awesome thanks!