check entire workbook for errors

[daveyc18]

looking for code to check for errors (n/a , ref# etc ) in the entire workbook then if it finds any ...write the sheet name in column A, cell address (eg a1) in column. B in a worksheet called "errors"

 

[offthelip]

try trhis code:

Sub test()
indi = 2
For i = 1 To Worksheets.Count
 If Worksheets(i).Name <> "errors" Then
  With Worksheets(i)
    nm = .Name
    Lc = .Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    lr = .Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    inarr = .Range(.Cells(1, 1), .Cells(lr, Lc))
  End With
   For j = 1 To lr
    For k = 1 To Lc
      If IsError(inarr(j, k)) Then
       With Worksheets("errors")
        Addr = .Range(.Cells(j, k), .Cells(j, k)).Address
        .Range(.Cells(indi, 1), .Cells(indi, 1)) = nm
        .Range(.Cells(indi, 2), .Cells(indi, 2)) = Addr
        indi = indi + 1
       End With
      End If
    Next k
   Next j
 End If
Next i

End Sub

 

[daveyc18]

try trhis code:

Sub test()
indi = 2
For i = 1 To Worksheets.Count
 If Worksheets(i).Name <> "errors" Then
  With Worksheets(i)
    nm = .Name
 [B]   Lc = .Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1[/B]
    lr = .Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    inarr = .Range(.Cells(1, 1), .Cells(lr, Lc))
  End With
   For j = 1 To lr
    For k = 1 To Lc
      If IsError(inarr(j, k)) Then
       With Worksheets("errors")
        Addr = .Range(.Cells(j, k), .Cells(j, k)).Address
        .Range(.Cells(indi, 1), .Cells(indi, 1)) = nm
        .Range(.Cells(indi, 2), .Cells(indi, 2)) = Addr
        indi = indi + 1
       End With
      End If
    Next k
   Next j
 End If
Next i

End Sub

tks!

it works, but eventually it sets object variable or with variable not set after all the errors are found

 

[daveyc18]

tks!

it works, but eventually it sets object variable or with variable not set after all the errors are found

i think its because one of the worksheets is blank

 

[daveyc18]

i think i got it...tks for your help...helped me

indic = 2
Dim ws As Worksheet, r As Range

For Each ws In Worksheets
    For Each r In ws.UsedRange
        If IsError(r.Value) Then
        
        
        
         Sheets("errors").Range("a" & indic) = r.Parent.Name
        Sheets("errors").Range("b" & indic) = r.Address
        
       indic = indic + 1
       
     
        End If
    Next
Next