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

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

"tofy" wrote:

> 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

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



--
Regards,
Tom Ogilvy





"tofy" wrote:

> 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

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


--
Don Guillett
SalesAid Software
(E-Mail Removed)
"tofy" <(E-Mail Removed)> wrote in message
news:63FCA57A-A47A-4714-8C7D-(E-Mail Removed)...
> 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