Adjust Search Macro to Search Across Multiple Sheets

Craigside · Dec 16th 2016

Hi Guys

I have this macro (as per below) that I use to 'locate' a "Ref Number" within another sheet. I would like the macro to be able to search multiple sheets;

If it finds the result in 1st sheet it stops (as it does now).
If it doesn't find in 1st sheet, it goes to 2nd sheet and continues, if it doesn't find in 2nd sheet it goes to 3rd sheet and continues.

Code

Sub FindCanBo()
'
' Select FindCanbo Macro
'


Dim x As String


x = ActiveCell.Value   'set the value of the activecell, unique ref number cell selected to x
With Sheets("CanBo")    'with the worksheet CanBo
    .Select  'select the worksheet
    .Range("A1:AB5000").Find(what:=x, after:=.Range("A1")).Activate   'find the cell whose value is equal to x and activate it
End With


End Sub

Display More

I don't know how to do the code myself, any assistance much appreciated.
Thanks Rob

**KjBox** · Dec 16th 2016

Re: Adjust Search Macro to Search Across Multiple Sheets

Try this:

Code

Sub FindCanBo()
     '
     ' Select FindCanbo Macro
     '
     
    Dim x As String, ws As Excel.Worksheet, r As Range
     
    x = ActiveCell.Value 'set the value of the activecell, unique ref number cell selected to x
    For Each ws In ThisWorkbook.Sheets
        With ws.Range("A1:AB5000")
            Set r = .Find(what:=x, After:=.Range("A1")) 'find the cell whose value is equal to x and activate it
            If Not r Is Nothing And ws.Name <> ActiveSheet.Name Then
                Application.Goto ws.r
                Exit Sub
            End If
        End With
    Next
     
End Sub

Display More

Craigside · Dec 16th 2016

Re: Adjust Search Macro to Search Across Multiple Sheets

Thank you for your speedy reply. I need to be able to specify in the code which sheets I need it to search?
I now suspect I wasn't quite specific enough in which case please accept my sincere apologies, I don't want to waste anyone's time. I was trying to be concise without any excess wording...

**KjBox** · Dec 16th 2016

Re: Adjust Search Macro to Search Across Multiple Sheets

Try this

Code

Sub FindCanBo()
     '
     ' Select FindCanbo Macro
     '
     
    Dim SheetsToSearch, x As String, ws As Excel.Worksheet, r As Range
    SheetsToSearch = Array("abc", "Def", "g.h_i") '// Enter the exact sheet names of sheets to be searched
     
    x = ActiveCell.Value 'set the value of the activecell, unique ref number cell selected to x
    For Each ws In ThisWorkbook.Sheets
        If Not IsError(Application.Match(ws.Name, SheetsToSearch, 0)) Then
            With ws.Range("A1:AB5000")
                Set r = .Find(what:=x, After:=.Range("A1")) 'find the cell whose value is equal to x and activate it
                If Not r Is Nothing Then
                    Application.Goto ws.r
                    Exit Sub
                End If
            End With
        End If
    Next
     
End Sub

Display More

Obviously the sheet names above are just examples and should be replaced with actual sheet names!

If you only have a few sheets NOT to be searched and many that DO need to be searched then it may be easier to use this

Code

Sub FindCanBo()
     '
     ' Select FindCanbo Macro
     '
     
    Dim SheetsNotToSearch, x As String, ws As Excel.Worksheet, r As Range
    SheetsNotToSearch = Array("abc", "def", "ghi") '// Enter the exact sheet names of sheets NOT to be searched
     
    x = ActiveCell.Value 'set the value of the activecell, unique ref number cell selected to x
    For Each ws In ThisWorkbook.Sheets
        If IsError(Application.Match(ws.Name, SheetsNotToSearch, 0)) Then
            With ws.Range("A1:AB5000")
                Set r = .Find(what:=x, After:=.Range("A1")) 'find the cell whose value is equal to x and activate it
                If Not r Is Nothing Then
                    Application.Goto ws.r
                    Exit Sub
                End If
            End With
        End If
    Next
     
End Sub

Display More

Craigside · Dec 16th 2016

Re: Adjust Search Macro to Search Across Multiple Sheets

Hi, thanks.

I've put 3 of my sheet names in, when run it throws up an error?

[ATTACH=CONFIG]70904[/ATTACH]

Code

Sub FindInLists()
     '
     ' Select FindInLists Macro
     '
     
    Dim SheetsToSearch, x As String, ws As Excel.Worksheet, r As Range
    SheetsToSearch = Array("abc", "Def", "Detail List.CanBo.BFA") '// Enter the exact sheet names of sheets to be searched
     
    x = ActiveCell.Value 'set the value of the activecell, unique ref number cell selected to x
    For Each ws In ThisWorkbook.Sheets
        If Not IsError(Application.Match(ws.Name, SheetsToSearch, 0)) Then
            With ws.Range("A1:AB5000")
                Set r = .Find(what:=x, After:=.Range("A1")) 'find the cell whose value is equal to x and activate it
                If Not r Is Nothing Then
                    Application.Goto ws.r
                    Exit Sub
                End If
            End With
        End If
    Next
     
End Sub

Display More

**KjBox** · Dec 16th 2016

Re: Adjust Search Macro to Search Across Multiple Sheets

Change

Code

Application.GoTo ws.r

to

Code

ws.Activate: r.Activate

Craigside · Dec 16th 2016

Re: Adjust Search Macro to Search Across Multiple Sheets

Hi

I tried this and I can't see anything happen? Also, I tested with a 'known result' and it didn't select the field in the sheet?

Thank you

**KjBox** · Dec 16th 2016

Re: Adjust Search Macro to Search Across Multiple Sheets

Put this in the Worksheet Object Module for the sheet which has the values to be searched for, then, rather that just selecting the cell containing the value to be searched for, double click that cell.

Code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim SheetsToSearch, ws As Excel.Worksheet, r As Range
    SheetsToSearch = Array("Sheet2", "Sheet4", "Sheet5") '// Enter the exact sheet names of sheets to be searched
     
    For Each ws In ThisWorkbook.Sheets
        If Not IsError(Application.Match(ws.Name, SheetsToSearch, 0)) Then
            With ws.Range("A1:AB5000")
                Set r = .Find(Target, [a1], xlValues, xlWhole, xlByRows, , True) 'find the cell whose value is equal to x and activate it
                If Not r Is Nothing Then
                    ws.Activate: r.Activate
                    Exit Sub
                End If
            End With
        End If
    Next
     
End Sub

Display More

See the attached file to see it working.

**KjBox** · Dec 16th 2016

Re: Adjust Search Macro to Search Across Multiple Sheets

When you enter your actual sheet names to be searched make sure that each is an exact replica of the sheet name, it is case sensitive. Also check that none of the actual sheet names have leading or trailing spaces.

Craigside · Dec 16th 2016

Re: Adjust Search Macro to Search Across Multiple Sheets

Thank you! Thats awesome!

**KjBox** · Dec 16th 2016

Re: Adjust Search Macro to Search Across Multiple Sheets

You're welcome.

Craigside · Dec 17th 2016

Re: Adjust Search Macro to Search Across Multiple Sheets

Hi
I can't upload an image in PM, so here it is... I am not seeing the 'Star' or reputation link on the website in either FF, IE or on my iPhone browser?

Thanks

[ATTACH=CONFIG]70915[/ATTACH]

**Reafidy** · Dec 17th 2016

Re: Adjust Search Macro to Search Across Multiple Sheets

Hi Craigside. You should see the reputation button now. Thanks.

Adjust Search Macro to Search Across Multiple Sheets

Participate now!

Share