L
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
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