using vba so search multiple Sheets

[Lorcan Dene]

i'm trying to search across multiple sheets based on data submitted via an
input box. So essentially, you click the button, a box appears, you type what
your looking for and if it finds it, it'll select it otherwise a error
message appeats.

i found the below code, but it only works if the cell with the value in it
is active (i.e. i've clicked on it).

Code:
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter the value to search for")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Value = datatoFind Then Exit For
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If


thanks in advance

 

[Ryan H]

This code will scan each worksheet in the activeworkbook looking for the
first instance of the text entered into the Input Box. If the text isn't
found in the entire workbook a message book appears telling the user it
wasn't found. Hope this helps! If so, let me know, click "YES" below.

Option Explicit

Sub FindData()

Dim MyData As String
Dim wks As Worksheet
Dim rngFoundData As Range

' get users data
MyData = InputBox("Please enter the value to search for.")
If MyData = "" Then Exit Sub

' search all sheets in workbook
For Each wks In Worksheets

' find data in current worksheet
Set rngFoundData = wks.Cells.Find(What:=MyData, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

' select found data and exit sub
If Not rngFoundData Is Nothing Then
wks.Activate
rngFoundData.Select
Exit Sub
End If

Next wks

' tell user data wasn't found
If rngFoundData Is Nothing Then
MsgBox MyData & " was not found in " & ActiveWorkbook.Name,
vbInformation
End If

End Sub

 

[Lorcan Dene]

very much appreciated.

thanks!!

This code will scan each worksheet in the activeworkbook looking for the
first instance of the text entered into the Input Box. If the text isn't
found in the entire workbook a message book appears telling the user it
wasn't found. Hope this helps! If so, let me know, click "YES" below.

Option Explicit

Sub FindData()

Dim MyData As String
Dim wks As Worksheet
Dim rngFoundData As Range

' get users data
MyData = InputBox("Please enter the value to search for.")
If MyData = "" Then Exit Sub

' search all sheets in workbook
For Each wks In Worksheets

' find data in current worksheet
Set rngFoundData = wks.Cells.Find(What:=MyData, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

' select found data and exit sub
If Not rngFoundData Is Nothing Then
wks.Activate
rngFoundData.Select
Exit Sub
End If

Next wks

' tell user data wasn't found
If rngFoundData Is Nothing Then
MsgBox MyData & " was not found in " & ActiveWorkbook.Name,
vbInformation
End If

End Sub