Find all instances and give link

gaj104

Well-known Member
Joined
Nov 9, 2002
Messages
864
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
assuming the data was in columnA of sheet named data.
try;
Code:
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

Well-known Member
Joined
Nov 9, 2002
Messages
864
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.
Code:
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
 

Forum statistics

Threads
1,136,272
Messages
5,674,748
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top