For Next statement error handling

sriche01

Board Regular
Joined
Dec 24, 2013
Messages
136
Office Version
  1. 365
Platform
  1. Windows
So I am working on code that has three nested For Next statements. It is meant to loop through every direct precedent of every cell of every column in a data set.
I am having trouble with my error handling. In most cases I can tell it to skip cells with no formula because they would have no precedent. The problem comes when I hit a cell with a formula, but no precedents as in

= 45/70

When this happens it throws a run time error "No cells were found". So I did some error handling. Here's my code:

VBA Code:
Sub Orders_Row_Check()
    Dim wb As Workbook, ws As Worksheet, ec As Worksheet
    Dim myRange As Range
    Dim rngPrecedents As Range, cell As Range
    Dim rngPrecedent As Range
    Dim row As Integer, lastrow As Integer, column As Integer, lastcolumn As Integer
    Dim adrs As String, logR As Integer
    
    Set wb = ActiveWorkbook
    Set ws = wb.Worksheets("Orders")
    Set ec = wb.Worksheets("Error Checking")
    
    row = ws.Range("Order_Invoice_Num").row
    lastrow = ws.Range("A" & ws.Rows.Count).End(xlUp).row
    lastcolumn = ws.Cells(row - 1, ws.Columns.Count).End(xlToLeft).column
    logR = 5
  Application.ScreenUpdating = False
  For column = 1 To lastcolumn
    If column <> 26 And column <> 27 And column <> 37 Then
    For row = ws.Range("Order_Invoice_Num").row To lastrow
        On Error GoTo Errorhandler
        Set cell = ws.Cells(row, column)
         On Error GoTo Nextcell
       If cell.HasFormula = True Then
       On Error GoTo Errorhandler
        
Set rngPrecedents = cell.DirectPrecedents  'This is the line that throws the error
   
 On Error GoTo Errorhandler
        For Each rngPrecedent In rngPrecedents
            
            If rngPrecedent.row <> cell.row Then
                If adrs <> cell.Address Then
                    adrs = cell.Address
                    ec.Cells(logR, 8) = adrs
                    ec.Hyperlinks.Add Anchor:=ec.Cells(logR, 8), _
                                Address:="", _
                                SubAddress:="Orders!" & adrs
                                
                    logR = logR + 1
                End If
            End If
         
        Next rngPrecedent
       End If
Nextcell:
    
    Next row
    End If
  Next column
  Application.ScreenUpdating = True
    MsgBox "all cells checked"
    Exit Sub
    
Errorhandler:
    Debug.Print cell.Address  'I wanted to see if it was actually reaching the error handler and it is, but it still stops me with the run time error instead of going to the next row.  
   GoTo Nextcell
End Sub

I feel like I'm missing something simple. Any thoughts? Thanks!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Ok, I feel stupid. It should have been resume Nextcell not Goto in the Error handler.
 
Upvote 0

Forum statistics

Threads
1,214,824
Messages
6,121,784
Members
449,049
Latest member
greyangel23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top