how do i use find in VB

[Guest]

Dear sir,
I am trying to use VB to find data, not in the same worksheet but in whole
workbook.
I programed in VB as the following:

Sub Button1_Click()
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate

End Sub

Well, it works to find data at the active worksheet but I need to find data
at different worksheets(all workbook) I tried to use
Xlsearchwithin.Xlwithinworkbook
but it keeps giveing an error.
Please could you help me.
Yours
Taufik

 

[Guest]

You can use somethjing like this to get every worksheet.

For Each MyWorksheet In Workbooks(Summary_Spreadsheet).Worksheets
If StrComp(PLOTDATAWORKSHEETNAME, MyWorksheet.Name) = 0 Then
SheetExist = True
Exit For
End If
Next MyWorksheet

 

[Guest]

Sub Button1_Click()
Dim sh as Worksheet
Dim rng as Range
Dim s as String
s = "*"
for each sh in worksheets
set rng = sh.Cells.Find(What:=s, _
After:=sh.Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext,
MatchCase:=False)
if not rng is nothing then
application.Goto rng, true
msgbox "Found at: " & rng.Address(0,0,xlA1,true)
end if
Next
End sub

 

[Don Guillett]

from a post by Julian Milano
Sub SearchBook()
Found = False

what = InputBox("What do you want to find? ")
' Ask what the user wants to find

For Each ws In Worksheets
Set foundcell = ws.Columns.Find(what)
' Go thru each worksheet and find 'what'

If Not (foundcell Is Nothing) Then
'I found it!

Found = True
MsgBox "The word was found in cell " & ws.Name & foundcell.Address
' Show me!
ws.Activate
' Activate the sheet it was found on....
ws.Range(foundcell.Address).Select
' And go to that cell
End If
Next ws

If Not Found Then
MsgBox "The word was not found"
Else
End If

End Sub