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
try trhis code:
VBA 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
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