danovkos
07-08-2010, 11:52 PM
Hi all,
i try to search value in whole workbook (all sheets). I used google and found this code.
But it doesnt works for me.
- it does not search if i paste my value in input box or if i declare it as variable (return error - "method "Goto" of object _Application failed"
-sometimes (i dont know when) it does not found value - it return - "not found" - :(
In my code i will search this value:
pval = Range("B1").Value
which in in wb1 and search after opening in wb2.
pls. can you help me?
thank you very much
Sub FindAllSheets()
Dim Found As Range, ws As Worksheet, LookFor As Variant
LookFor = InputBox("Enter value to find")
If LookFor = "" Then Exit Sub
For Each ws In ActiveWorkbook.Worksheets
Set Found = ws.Cells.Find(what:=LookFor)
If Not Found Is Nothing Then Exit For
Next ws
If Found Is Nothing Then
MsgBox LookFor & " not found.", vbExclamation
Else
Application.Goto reference:=Found, Scroll:=True
End If
End Sub
i try to search value in whole workbook (all sheets). I used google and found this code.
But it doesnt works for me.
- it does not search if i paste my value in input box or if i declare it as variable (return error - "method "Goto" of object _Application failed"
-sometimes (i dont know when) it does not found value - it return - "not found" - :(
In my code i will search this value:
pval = Range("B1").Value
which in in wb1 and search after opening in wb2.
pls. can you help me?
thank you very much
Sub FindAllSheets()
Dim Found As Range, ws As Worksheet, LookFor As Variant
LookFor = InputBox("Enter value to find")
If LookFor = "" Then Exit Sub
For Each ws In ActiveWorkbook.Worksheets
Set Found = ws.Cells.Find(what:=LookFor)
If Not Found Is Nothing Then Exit For
Next ws
If Found Is Nothing Then
MsgBox LookFor & " not found.", vbExclamation
Else
Application.Goto reference:=Found, Scroll:=True
End If
End Sub