How to Find value in all worksheets in a single workbook

[Nitesh Mathur]

HI,

Im using MS Excel 2007 and try to find out a macro for FIND a value in all sheets in a single workbook.

Query:

Im at Sheet1 where I created a Command button and by pressing this, one Inputbox appears in which I want to enter emp no (lets say 1234). Then a macro can run and FIND in sheet2, sheet3, sheet4, sheet5 and place a cursor at cell where the value is found.


Thanks
Niteshm

 

[Chihiro]

While it is possible, I'd not recommend it. Unless you are doing some operation that requires cell values/addresses to be stored as variable.

It's much more efficient to use CTRL+F and use default search. You can jump to each cell easily enough.

Just set search parameter to be Within: Workbook, Look in: Formulas or Value as needed.

 

[Nitesh Mathur]

While it is possible, I'd not recommend it. Unless you are doing some operation that requires cell values/addresses to be stored as variable.

It's much more efficient to use CTRL+F and use default search. You can jump to each cell easily enough.

Just set search parameter to be Within: Workbook, Look in: Formulas or Value as needed.

View attachment 22521

HI Chihiro,

Thanks for your reply, but this does not fulfill my requirement.
I need a macro for this because its my tool requirement, which I m developing in excel.

Thanks

Niteshm

 

[Chihiro]

Okie, I have few questions.

1) Will there be more than single instance of value you are searching for in a sheet? Or is it single occurrence in each sheet, but occurs across multiple sheets?

2) If more than one occurrence in a sheet, do you need first or last occurrence selected?

Also, can you upload sample sheet so I can see how data is organized.

 

[Nitesh Mathur]

Okie, I have few questions.

1) Will there be more than single instance of value you are searching for in a sheet? Or is it single occurrence in each sheet, but occurs across multiple sheets?

2) If more than one occurrence in a sheet, do you need first or last occurrence selected?

Also, can you upload sample sheet so I can see how data is organized.

======================================
Answers of you questions:
1. Only Single instance of value and are unique. If search value found in one sheet then not found in another sheets.
2. As I told in point no1, that single instance and unique value.

Sample sheet uploaded.
In Sheet1 when I press button for find value , a inputbox appear and I put the value (a seating number) in it and it search the value in all sheets but not found the value and reached at the end of sheet.


My Query is:

Im at Sheet1 where I created a Command button and by pressing this, one Inputbox appears in which I want to enter no (lets say 1234). Then a macro can run and FIND in sheet2, sheet3, sheet4, sheet5 and place a cursor at cell where the value is found.


Thanks
Nitesh

 

[Marc L]

Hi !

It's odd you want to find a number that is not in the worksheets !

Just use Find method like in the example in VBA inner help …​

 

[Nitesh Mathur]

Hi !

It's odd you want to find a number that is not in the worksheets !

Just use Find method like in the example in VBA inner help …​

Sorry Marc... number could be any , which is available in a workbook only.

But any way I have found the code Thanks


Sub Find_Val()
Dim rngFound As Range
Dim wks As Worksheet
Dim whtfind As String
whtfind = InputBox("Please enter the seat no")

For Each wks In Worksheets
Set rngFound = wks.Cells.Find(What:=whtfind, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rngFound Is Nothing Then Exit For
Next wks

If rngFound Is Nothing Then
MsgBox ("Seat no " & whtfind & " not found !")
Else
rngFound.Parent.Select
rngFound.Select
End If
End Sub

 

[Marc L]

Please, use code tags ‼
Not bad your code …

Way starting to search from worksheet #2 :​

Private Sub CommandButton1_Click()
Dim Rf As Range
SN$ = InputBox(vbLf & vbLf & "Seat # ?", "  Hall search")
If SN = "" Then Exit Sub

For W& = 2 To Worksheets.Count
    With Worksheets(W)
        Set Rf = .UsedRange.Find(SN, , xlValues, xlWhole)
     If Not Rf Is Nothing Then .Activate: Rf.Select: Exit For
    End With
Next

If Rf Is Nothing Then Beep Else Set Rf = Nothing
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Chihiro]

Glad you found solution. I'd use .UsedRange instead of .Cells myself.
Also, if you end up putting some info in Sheet1, below code will make it slightly more efficient.

Sub Find_Val()
Dim FindString As String
Dim Rng As Range
Dim ws As Worksheet
Dim FirstFound As String

FindString = InputBox("Enter a Search value")
    If Trim(FindString) <> "" Then
        For Each ws In ThisWorkbook.Worksheets
        With ws
            If ws.Name = "Sheet1" Then GoTo myNext
                Set Rng = .Cells.Find(What:=FindString, _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    MatchCase:=False)
          
myNext:
        End With
        If Not Rng Is Nothing Then Exit For
        Next ws
      
        If Not Rng Is Nothing Then
                FirstFound = Rng.Address
                Rng.Parent.Select
                Rng.Select
        Else
            MsgBox "Unable to find " & FindString
        End If
    End If
End Sub

MarcL beat me to it and more elegant code.

 

[Nitesh Mathur]

Please, use code tags ‼
Not bad your code …

Way starting to search from worksheet #2 :​

Private Sub CommandButton1_Click()
Dim Rf As Range
SN$ = InputBox(vbLf & vbLf & "Seat # ?", "  Hall search")
If SN = "" Then Exit Sub

For W& = 2 To Worksheets.Count
    With Worksheets(W)
        Set Rf = .UsedRange.Find(SN, , xlValues, xlWhole)
     If Not Rf Is Nothing Then .Activate: Rf.Select: Exit For
    End With
Next

If Rf Is Nothing Then Beep Else Set Rf = Nothing
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

Thanks Marc