Solved: Searching in multiple workbooks

[debkev1010]

I have 2 workbooks in which I'm trying to perform a search in both for the same string. One workbook contains fault names and their attributes (Workbook B.xlsx). The other workbook contains requirements with the fault names (Workbook A.xlsm). I've created a UserForm (in Workbook A.xlsm) for the user to insert the fault name to search for. One search will display the fault's color and code and the other search will display the requirement that the fault is in. I've having a problem with the actual search logic (Run-time error '91': Object variable or With block variable not set):

Cells.Find(what:=TextBox3, LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False, searchformat:=False).Activate

I've attached the 1 workbook that I launch the Form from. I can't seem to upload more than one file so here's the data from the other workbook:

Fault                 Color                 Code 
System Fail         Red                     1 
Subsystem Fail   Yellow                   2

Thanks,
Kevin

[geekgirlau]

Private Sub Search_Click()
'Declarations
Dim elsFile As Workbook
Dim reqFile As Workbook
Dim spreadSheet As Excel.Application
'Open fault file (workbook b.xls)
Set elsFile = Workbooks.Open("C:\Documents and Settings\johnske14\Desktop\workbook b.xlsx")
'Search fault file for fault name entered in the form
Cells.Find(What:=TextBox3, LookIn:=xlFormulas, LookAt:=xlPart).Activate
        
'Assign attributes of fault
TextBox6 = Cells(ActiveCell.Row, ActiveCell.Column + 1)
TextBox5 = Cells(ActiveCell.Row, ActiveCell.Column + 2)
'search requirement file (workbook a.xls) for the requirement that contains the fault
Set reqFile = ThisWorkbook
reqFile.Activate
Cells.Find(What:=TextBox3, LookIn:=xlFormulas, LookAt:=xlPart).Activate
'display requirement in user form
TextBox4 = Cells(ActiveCell.Row, ActiveCell.Column)
End Sub

[debkev1010]

Hey thanks, geekgirlau,

Your code cleared it up. At first I added all of your code with the exception of the last "Cells.Find" line. I was still using the long Find list I had in my code and getting errors stating some Object With stuff. When I copied your "Find", it cleared up...why is that? It looks like the only difference between the two was additional search parameters. Thanks again

[geekgirlau]

Your search used "LookAt:=xlWhole", while my search uses "LookAt:=xlPart". My search will basicially find the search term if it forms any part of the formula in the cell.

When you use the macro recorder, it records all parameters in a dialog box. For example, if I record a macro to change my point size by going into the Format Cell dialog, I get this (recorded in 2007 so there'll be a couple of extra parameters):

With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With

In this case, all I actually need is:

Selection.Font.Size = 12

Oh, and I wouldn't use Selection either, although that's a whole different discussion!

At any rate, using the macro recorder is a great way to learn the Excel object model, or any of the MS suite for that matter. However most of the time you only need to keep the code dealing with what you are attempting to achieve. All the extra bits are unnecessary fill at best, and sometimes will lead to undesired results.