VBA Code to Search for a Cell reference within multiple sheets

[Connall]

Hi there,

I am currently working to have a spreadsheet that has a macro that can search for a set cell value in multiple other sheets, then jump to that found cell.#

For example, cell $G$15. A user can enter a six digit code in here, then click a macro button. The macro will search for the instance of this code in a set of 9 other sheets within the same workbook, and then jump the user to that cell.

I have managed before to have this code work when searching within one sheet, but i cannot adapt it properly to search multiple sheets. This is my attempt to adapt, but it does not work.

Any help would be greatly appreciated.

Thanks in advance.

CODE

Sub Find_First()
    Dim FindString As String
    Dim Rng As Range
    Dim sh As Worksheet
    FindString = Sheets("Index").Range("Q12").Value
    If Trim(FindString) <> "" Then
    For Each sh In ActiveWorkbook.Worksheets
        With sh.Range("A:A")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
End Sub

 

[steve the fish]

Hi. It was virtually there. You missed your 'next' off the loop and also ive moved the message box or you would get one for each sheet that was present.

Sub Find_First()

    Dim FindString As String
    Dim Rng As Range
    Dim sh As Worksheet
    FindString = Sheets("Index").Range("Q12").Value
    If Trim(FindString) <> "" Then
        For Each sh In ActiveWorkbook.Worksheets
            With sh.Range("A:A")
                Set Rng = .Find(What:=FindString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not Rng Is Nothing Then
                    Application.Goto Rng, True
                    Exit Sub
                End If
            End With
        Next
    End If
    MsgBox "Nothing found"
    
End Sub

 

[Connall]

Thanks for your swift reply Steve, i pasted your revised code in, however recieved this error:

Run-time error '1004'
method 'goto of object'_application' failed.

Also, based from my code, rather than your alteration, i am worried that even when working, it may search, find the value in the actual cell i am typing it in (Q12 of sheet 'INDEX') and remain on this page, as this search function is on the front most page of the workbook.
Will this happen with this code when it is working? when i had it working searching one sheet, i didn't have this problem as the sheet was defined by name.

Thanks again for your time to help.

 

[steve the fish]

Is the worksheet hidden? That would produce that error. You don't need to define the sheet name in the goto. Your variable 'Rng' will have a sheet associated with it.

 

[Connall]

the worksheet searching from isn't hidden, and the worksheets i want to search in are not hidden, however there are other hidden worksheets within the workbook for other functions.

would this be causing the issue?

if so, is it possible to set the search and go to range as a specific set of worksheets by name, rather than all in the workbook?

 

[steve the fish]

You could name the sheets to be searched like this:

    mySheets = Array("Sheet1", "Sheet2", "Sheet3")
    FindString = Sheets("Index").Range("Q12").Value
    If Trim(FindString) <> "" Then
        For i = LBound(mySheets) To UBound(mySheets)
            With Sheets(mySheets(i)).Range("A:A")
                Set Rng = .Find(What:=FindString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not Rng Is Nothing Then
                    Application.Goto Rng, True
                    Exit Sub
                End If
            End With
        Next
    End If
    MsgBox "Nothing found"

 

[Connall]

Thanks Steve. This search and go to works like a charm

Your a Legend. i spent a lot of time searching forums for this solutions which basicly allows a generic search box to be used for multiple sheets, and nowhere could i find it. So i think it will be of great use to other users.

Thanks Again

COnnall