Search two sheets simultaneously

[cornishbloke]

Despite earlier help I'm still having problems with the following:

---------------------------------------------------------------
Sub Search()
'
Dim MyName As String
MyName = ActiveSheet.Range("E20").Value
Sheets("Engineers").Activate
Sheets("Engineers").Cells.Find(What:=MyName, _
LookAt:=xlPart, MatchCase:=False).Activate
End Sub
---------------------------------------------------------------

This macro currently takes the value of cell E20 in Sheet1 and searches
for this value in the 'Engineers' sheet.

However, I want the sheet to search both the engineers sheet and the
'office staff' sheets at the same time. Also, the macro currently
crashes if the name entered in E20 doesn't exist in either of the other
sheets.

Can anyone help? I should point out that I am completely new to vba!

 

[pfsardella]

Sub Search()
Dim MyName As String
Dim rngF As Range, rngF2 As Range

MyName = ActiveSheet.Range("E20").Value

On Error Resume Next
Set rngF = Sheets("Engineers").Cells.Find(What:=MyName, _
LookAt:=xlPart, MatchCase:=False)
Set rngF2 = Sheets("Office Staff").Cells.Find(What:=MyName, _
LookAt:=xlPart, MatchCase:=False)
On Error GoTo 0
If Not rngF Is Nothing Then
MsgBox "Found in Engineers"
Else
MsgBox "Not Found in Engineers"
End If

If Not rngF2 Is Nothing Then
MsgBox "Found in Office Staff"
Else
MsgBox "Not Found in Office Staff"
End If

End Sub
Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul