Find all instances and give link

[gaj104]

Hi,

I have dotted around my workbook various error checks which look like the following:

if A1=B1,"","@error not balanced"
if D2-D1=100,"","@error check figure"

etc

What I was hoping to do was write a macro which wil populate sheet called errors by returning all values that contain @error and provide a hyper link to to the cell for me to investigate.

Essentially its the same as doing a find all, using values, in workbook and returning the matches.

However I'm stuck as how to code this. Can anyone help?

Thanks

 

[agihcam]

assuming the data was in columnA of sheet named data.
try;

Sub sample()
Dim i As Long
With Sheets("data").Columns("a")
Set c = .Find("*@error*", , , xlPart)
    If Not c Is Nothing Then
    f = c.Address
    Do
        Sheets("errors").Range("a" & Rows.Count).End(xlUp).Offset(1) = c.Value
        temp = c.Address(0, 0)
        Sheets("errors").Hyperlinks.Add anchor:=Sheets("errors").Range("a" & Rows.Count).End(xlUp), Address:="", SubAddress:="data!" & temp
        Set c = .FindNext(c)
        Loop Until f = c.Address
    End If
End With
End Sub

 

[gaj104]

Thanks agihcam for the code.

I tweaked it a bit as it covers the entire workbook (so loops through all the worksheets) and works a charm.

Sub checkErrors()

Dim wb As Workbook
Dim i As Long

Worksheets("Errors").Cells.Clear

Set wb = ActiveWorkbook
For j = 1 To wb.Sheets.Count
    If wb.Sheets(j).Name <> "Errors" Then

        Set c = wb.Sheets(j).UsedRange.Find("*@error*", , , xlPart)
        
        If Not c Is Nothing Then
        f = c.Address
            Do
                Sheets("Errors").Range("a" & Rows.Count).End(xlUp).Offset(1) = c.Value
                temp = wb.Sheets(j).Name & "!" & c.Address(0, 0)
                Sheets("Errors").Hyperlinks.Add anchor:=Sheets("errors").Range("a" & Rows.Count).End(xlUp), Address:="", SubAddress:=temp, TextToDisplay:=Chr(32) & c.Value & Chr(32)
                Set c = wb.Sheets(j).UsedRange.FindNext(c)
                Loop Until f = c.Address
        End If
        
    End If

Next

End Sub

Many thanks