search value in entire wb - only fixing code

[danovkos]

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

[mdmackillop]

You may want to set more find paramaters, as these can be inherited from previous Finds , eg LookAt, Match Case, LookIn etc.
I would also Dim LookFor as a string.

[danovkos]

I dont know if i good understand you.
But i changed this line:


Dim Found As Range, ws As Worksheet, LookFor As String


but there is still the same error.

For me is OK, if it will search just first value and goto founded the cell. If is there more the one value, it doesnt matter.

this is my real code



Sub Odpis()
Dim C As Range
Dim pval, pval2
pval = Range("B1").Value
pval2 = Range("E1").Value
Dim FindString As String
Dim Rng As Range
Dim FirstAddress As Range
Dim Found As Range, ws As Worksheet, LookFor As String
Workbooks.Open FileName:= _
"C:path\path2\file.xls" _
, UpdateLinks:=0

If pval = "sem CIF" Then Exit Sub
If pval <> "sem CIF/č.ú" Then
LookIn:=xlFormulas, LookAt:=xlWhole)
LookFor = pval
' 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 If
'Cells(ActiveCell.Row, "BN").Select
'CenterOnCell Cells(ActiveCell.Row, "BN")
End Sub

[danovkos]

now i exteded the find parameters and now i have

Set Found = ws.Cells.Find(what:=LookFor, LookIn:=xlFormulas, LookAt:=xlWhole)

but still the same error, but only sometimes :(

edit: now i figured out, that it works only if the value is in wb only once. If is twice or more it return error :(
How to fix it. I mean - it will not care if it there more or only one values which i looking for.

thank you

[mdmackillop]

Try this. I think there is an error in you file path, missing a "\".


Option Explicit
Sub Odpis()
Dim pval
Dim Rng As Range
Dim Found As Range, ws As Worksheet, LookFor As String
Dim WB As Workbook


pval = Range("B1").Value

Set WB = Workbooks.Open(Filename:="C:\AAA\file.xls", UpdateLinks:=0) '<== Fix file name
If pval = "sem CIF" Then Exit Sub
If pval <> "sem CIF/c.ú" Then

LookFor = pval
If LookFor = "" Then Exit Sub
For Each ws In WB.Worksheets
Set Found = ws.Cells.Find(what:=LookFor, LookIn:=xlFormulas, LookAt:=xlWhole)
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 If
End Sub