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:
I feel like I'm missing something simple. Any thoughts? Thanks!
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!