On Error GoTo Issue with Worksheet Function

MarshHill

New Member
Joined
Jul 2, 2015
Messages
6
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,351
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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:

Code:
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

New Member
Joined
Jul 2, 2015
Messages
6
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:

Code:
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

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
you could something like the below. this will give you an idea as to where to go to complete the task.

How it works


Code:
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

New Member
Joined
Jul 2, 2015
Messages
6
I didn't use all of your code, but you gave me a few ideas and I got it to work!

Thanks a bunch.
 

Forum statistics

Threads
1,082,295
Messages
5,364,359
Members
400,792
Latest member
Dxmiian

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top