On Error GoTo Issue with Worksheet Function

[MarshHill]

Hello,

I am having issues with the following code in Excel 2010:

Dim i As Long
Dim NewBottomRow as Double
Dim Row as String
For i = 1 To NewBottomRow - 1
On Error GoTo Hop
Row = Split(ActiveCell(1).Address(1, 0), "$")(1)
Selection.Value = WorksheetFunction.Average(Range("A" & Row, ActiveCell.Offset(0, -1)))
Hop:
Selection.Offset(1, 0).Select
Next

When there are no issues with the numbers that the worksheetfunction.average is picking up, it runs fine. sometimes, however, there will be a line with no numbers and the average function will not work. I am trying to get the macro to skip the line without any numbers, but the "On Error GoTo Hop" has not been working for me. All variables are declared and my error trapping is not set up to stop at everything.

Any suggestions?

 

[RoryA]

You need a Resume statement to reset the error - Goto on its own is not enough. I'd skip the selection and use On Error Resume Next since all you want to do is ignore the error:

On Error Resume Next
For i = 1 To NewBottomRow - 1
    With ActiveCell.Offset(i - 1)
        .Value = WorksheetFunction.Average(Cells(.Row, "A"), .Offset(i - 1, -1))
    End With
Next
On Error GoTo 0

 

[MarshHill]

You need a Resume statement to reset the error - Goto on its own is not enough. I'd skip the selection and use On Error Resume Next since all you want to do is ignore the error:

On Error Resume Next
For i = 1 To NewBottomRow - 1
    With ActiveCell.Offset(i - 1)
        .Value = WorksheetFunction.Average(Cells(.Row, "A"), .Offset(i - 1, -1))
    End With
Next
On Error GoTo 0

Thanks Rory,

I am actually still having issues even with your correction. I had tried using an "On Error Resume Next" to no avail originally, and I did like the revisions you made to the code. But I am still getting "Run-time error '1001': Unable to get the Average property of the WorksheetFunction Class".

I'm considering just deleting the row if it has no data in it, but it would be nice to know why this will not work!

Again, thanks for the reply Rory,

-Marshall

 

[RCBricker]

you could something like the below. this will give you an idea as to where to go to complete the task.

How it works

Option Explicit

Sub avg()
Dim lrow As Long
Dim rng, cell, rng1, cell1 As Range
Dim i, j As Variant
Dim sh As Worksheet

'**************************************************************************************
'   set your sheet variable (this is down and dirty so i used activesheet - don't
'**************************************************************************************
Set sh = ActiveSheet

With sh
'**************************************************************************************
'   Find last row of data (substitute "A" with what ever column letter has your
'   complete data in it
'**************************************************************************************
    lrow = Range("A" & .Rows.Count).End(xlUp).Row
    
'**************************************************************************************
'   set rng range variable to the cell I want my average value to appear in
'**************************************************************************************
    Set rng = Range(.Cells(1, 3), .Cells(lrow, 3))

'**************************************************************************************
'   FOR EACH loop to move through each cell in my rng range
'**************************************************************************************
    For Each cell In rng
    
'**************************************************************************************
'   set rng1 to the cell one column to the left of the active cell in rng
'   set cell1 to the cell two columns to the left of the active cell in rng
'   !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'       if you need more columns then set your ranges to include the values
'   !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'**************************************************************************************
        Set rng1 = Cells(cell.Row, cell.Offset(, -1).Column)
        Set cell1 = Cells(cell.Row, cell.Offset(, -2).Column)
        
'**************************************************************************************
'   if both rng1 and cell1 are empty do nothing
'**************************************************************************************
            If rng1.Value = "" And cell1.Value = "" Then
            Else
'**************************************************************************************
'   if one or the other is empty then simply divide the non-empty cell by two
'**************************************************************************************
                If rng1.Value = "" Or cell1.Value = "" Then
                    If rng1.Value = "" Then
'**************************************************************************************
'   set your sheet variable (this is down and dirty so i used activesheet - don't
'**************************************************************************************
                        cell.Formula = WorksheetFunction.Sum(cell1 / 2)
                    Else
                        cell.Formula = WorksheetFunction.Sum(rng1 / 2)
                    End If
                Else
'**************************************************************************************
'   when both are occupied get your average
'**************************************************************************************
                    cell.Formula = WorksheetFunction.Average(rng1, cell1)
                End If
            End If
        
    Next cell
End With

End Sub

 

[MarshHill]

I didn't use all of your code, but you gave me a few ideas and I got it to work!

Thanks a bunch.