How to search all open workbooks

[TwistShout]

hello all, first-time poster, long-time reader.
(using excel 2007)

I was hoping to get some help with a small VBA problem I am having. The basic idea of my program will have an input box with a text input and "Go" button. When something is input and and "Go" is engaged, it will search all the open workbooks (and worksheets therein) for the string/number.

I am currently halfway there. The text box input works, but it will only search the active workbook. I know that it is probably a very simple code terminology problem, but I am having trouble finding out the proper way to phrase it.

The debug always catches it in the "cells.find(what......... " portion. That part is from a recorded macro (with the jerry term added in).

Here is my code so far.
(jerry is the text/string/number input from the user form)

Global jerry As String
Sub Macro1()

Dim wb As Workbook, gb As String, ws As Worksheet

UserForm1.Show

For Each wb In Workbooks

For Each ws In Worksheets

Cells.Find(What:=jerry, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:= xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Next ws

Next wb

End Sub

 

[doofusboy]

Have you tried putting "jerry" in quotes?

 

[TwistShout]

It still debugs to the "cell.find(what.........." portion.

I think the problem has to do with the "for each loop for wb" portion.
Like it is not doing the "cell.find(...." in each workbook during the loop, only the active workbook.

 

[doofusboy]

Have you tried telling it WHAT Cells? Something like this?

ws.Cells.Find(What:=jerry, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:= xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

 

[TwistShout]

Yes, just tried ws. and wb.ws.
Neither worked. I believe that the answer will be a little re-phrasing/clarification quirk along those lines tho. Just a matter of finding it.....

 

[Gary McMaster]

The "Activate" term of your "find" statement may be failing because the cell it is trying to activate in not in the active workbook and/or active worksheet.

You could try activating each book and sheet as shown in red:

Sub Macro1()

Dim wb As Workbook, gb As String, ws As Worksheet

UserForm1.Show

For Each wb In Workbooks

wb.Activate

For Each ws In wb.Worksheets

ws.Activate

ws.Cells.Find(What:=jerry, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Next ws

Next wb

End Sub

or maybe get rid of the "Activate" maybe something like this (then the book and sheet would not have to be active):

Sub Macro1()

Dim wb As Workbook, gb As String, ws As Worksheet
Dim oFound As Range

UserForm1.Show

For Each wb In Workbooks

For Each ws In wb.Worksheets

Set oFound = ws.Cells.Find(What:=jerry, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not oFound Is Nothing Then
    Debug.Print oFound.Address
End If

Next ws

Next wb

End Sub

Gary

 

[TwistShout]

Those last two didn't work. I'm going to have to keep playing with it to see if adjustments make a difference.

This whole thing is to make a "tool" for a co-worker. It's sort of a "i spend an hour on this and she saves 5/10 minutes having to search through data a few times a day." I'm the newbie so i have to get my "in" somewhere.