Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search two sheets simultaneously
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! --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search two sheets simultaneously
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 -------------------------------------------------------------------------------------------------------------- Be advised to back up your WorkBook before attempting to make changes. -------------------------------------------------------------------------------------------------------------- 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! --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
View sheets simultaneously? | Excel Discussion (Misc queries) | |||
how to duplex mulitple sheets simultaneously? | Excel Worksheet Functions | |||
How do I unprotect multiple sheets simultaneously? | Excel Discussion (Misc queries) | |||
How can I protect several sheets in a workbook simultaneously? | Excel Discussion (Misc queries) | |||
How to apply the same background to many sheets simultaneously. | Excel Worksheet Functions |