Excel Worksheet Search Problem (vba newbie) 2

[wraheem]

OK, I have a several workbooks in a folder that I need to write a macro that will search these workbooks worksheet by worksheet and alert the user that a match has been found.

There are many columns in these worksheets but the ones I'm focusing on is two columns next to one another in a Lastname Firstname format. Like:

Doe John
Doe Jane

Using some code I found, I am looping through the worksheets then doing a cells.find command like so:

Dim FirstName as string
Firstname = "John"
For Each WS In Worksheets
     With WS.Cells
     Set Cell = .Find(What:="Doe",LookIn:=xlValues,LookAt: _
=xlPart,MatchCase:=False, SearchOrder:=xlByColumns)
 If Not Cell Is Nothing And Cell.Offset(0, 1) = FirstName Then
FirstAddress = Cell.Address
                        MsgBox "Name Found"
                        Cell.Activate
                        Do
                            Counter = Counter + 1
                            ReDim Preserve FindCell(1 To Counter)
                            ReDim Preserve FindSheet(1 To Counter)
                            ReDim Preserve FindWorkBook(1 To Counter)
                            ReDim Preserve FindPath(1 To Counter)
                            ReDim Preserve FindText(1 To Counter)
                            FindCell(Counter) = Cell.Address(False, False)
                            FindText(Counter) = Cell.Text
                            FindSheet(Counter) = WS.Name
                            'FindWorkBook(Counter) = WB.Name
                            'FindPath(Counter) = WB.FullName
                            Set Cell = .FindNext(Cell)
                        Loop While Not Cell Is Nothing And Cell.Address <> FirstAddress
                    End If
                End With
            Next

Now if I have John Doe in several worksheets it will find then and open the msgbox. But after so many it will give an error that "Activate method out of range class". I'm assuming that the loop has gotten to a spreadsheet (say the 5th out of 5 sheets) and has no more sheets to search and gives the error.

Also the msgbox opens, say four times if there are 4 sheets even though the condition only matches in say, 2.

I know this is probably both confusing and "newbieish" but I have been peggin away at this since Friday I'm no futher along than when I started.

I took this task because I work in VB .Net but this is world away....

Thanks for any help...

 

[SkipVought]

Hi,

I'd switch these statements

....
            If Not Cell Is Nothing And Cell.Offset(0, 1) = FirstName Then
               FirstAddress = Cell.Address
               ws.Activate
               Cell.Select
               MsgBox "Name Found"
....

you FIRST need to activate the worksheet and THEN select the cell.

Skip,
[sub]
[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking. [/sub]

 

[rmikesmith]

Another suggestion for the same bit of code: Re-write this test

If Not Cell Is Nothing And Cell.Offset(0, 1) = FirstName Then

to be two nested Ifs like this:

If Not Cell Is Nothing Then
  If Cell.Offset(0, 1) = FirstName Then

The reason is that VBA does not support "short-circuit" boolean evaluation as some languages do. This means if Cell is Nothing the code will error out because the second test will attempt be evaluated but fail (the object is Nothing so none of its properties can be accessed).

Regards,
Mike

 

[wraheem]

Thanks to the both of you...Mike as soon as I got it working with Skip's help then I immediately got an error because of not nesting the if's

Thanks again...