Loop through sheet and find errors

[Ozas]

Hi all.

I am currently trying to loop through a worksheet and find all cells with ''ERR''
then generate a list of their locations in another spreadsheet.
I have been successful in generating the list next to the erroneous cells, but that is a test and it's really no good to me unless I can get the locations of the erroneous cells in a neat column in another sheet.

Would anyone be kind enough to assist?

Many thanks.

My current code below.

Sub FindError()
Dim sell As Range
Dim LastRow As Long
Dim Rng As Range
Application.ScreenUpdating = False
LastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
Set Rng = Worksheets("Sheet1").Range("A1:A" & LastRow)
For Each sell In Rng
If sell.Value = "ERR" Then
   sell.Offset(0, 1).Value = sell.Address(False, False)
End If
Next sell
Range("A1").Select
Application.ScreenUpdating = True
End Sub

 

[VoG]

Maybe like this

Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(i).Value = sell.Address(False, False)

 

[Andrew Poulsom]

This will put the results on a new worksheet:

Sub FindError()
    Dim sell As Range
    Dim LastRow As Long
    Dim Rng As Range
    Dim ShNew As Worksheet
    Dim r As Long
    Application.ScreenUpdating = False
    LastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
    Set Rng = Worksheets("Sheet1").Range("A1:A" & LastRow)
    Set ShNew = Worksheets.Add
    r = 1
    For Each sell In Rng
        If sell.Value = "ERR" Then
            ShNew.Cells(r, 1).Value = sell.Address(False, False)
            r = r + 1
        End If
    Next sell
    Range("A1").Select
    Application.ScreenUpdating = True
End Sub

By the way there is no harm in using cell (rather than sell) as the name of a Range variable. Contrary to popular belief it's not a word used by VBA.

 

[Ozas]

This will put the results on a new worksheet:

--

By the way there is no harm in using cell (rather than sell) as the name of a Range variable. Contrary to popular belief it's not a word used by VBA.

This works perfectly. Modified the code a tiny bit, since the destination sheet is known, I just pointed the results to it, as opposed to creating a new one.
Many thanks, sir.

Regarding your comment on cell/sell usage. I'm a heavy 'Delphi' user, that was instinctive