Articles

Excel Searc across workbooks

February 7, 2005 at 07:14:07
Specs: Win2k Pro, 1ghz 328

Is there a way to use the search function in Excel across workbooks?

I can get it to search sheets in a work book, but I need to search workbooks for each month.

EG: Jan, Feb, Mar, etc. looking for a particualr invoice #.

Larry


See More: Excel Searc across workbooks

Report •


#1
February 7, 2005 at 07:51:34

Of course yes, u've only got an advertisin before update.

Report •

#2
February 7, 2005 at 10:38:37

I'm not sure I understand the advertising part.

Does that mean there is way to let the program know that the info is stored in other workbooks?

Could I trouble you for an explanation or should I just do a search in Excel for the advertise function?
Thanks for the quick reply.

Larry


Report •

#3
February 7, 2005 at 14:10:49

Larry - I am pretty certain that you can ignore response 1. I don't think you can do such a search with the normal Excel functions. You could write a macro to do it though.

Report •

Related Solutions

#4
February 7, 2005 at 18:21:17

Grok Lobster:

Ha! Me write a macro.
That's funny.

OK, I'll try. Any leads on where I could look for instructions?

Larry


Report •

#5
February 8, 2005 at 09:03:05

I like your attitude so I am going to help you write it. Here is what I have started with

Sub SearchBooks()
For i = 1 To Workbooks.Count
Workbooks(i).Activate
Range("A1").Activate
SearchWord = InputBox("Enter the string to search for")
FindAnother:
Set WordAddress = Cells.Find(What:=SearchWord, after:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If WordAddress Is Nothing Then
MsgBox "Search string not found"
Else:

Address = WordAddress.Address
MsgBox Address
Range(Address).Activate
GoTo FindAnother
End If
Next i
End Sub

Open a new workbook and then press Alt+F11 to open the VB editor. Start a new module and paste in the above code. Press F5 to run the macro.
Right now it just keeps looping through the first workbook. We need to create an array to store the found cell locations and then check against the array to know when to switch to the next workbook. How much detail do you need on the results?


Report •

#6
February 8, 2005 at 09:25:09

Sub SearchBooks()
SearchWord = InputBox("Enter the string to search for")
For i = 1 To Workbooks.Count
Workbooks(i).Activate
Range("A1").Activate
FindAnother:
Set WordAddress = Cells.Find(What:=SearchWord, after:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If WordAddress Is Nothing Then
MsgBox ActiveWorkbook.Name & Chr(13) & "Search string not found"
Else:
If WordAddress.Address = CheckCell Then GoTo NextBook
If ActiveCell.Address = "$A$1" Then CheckCell = WordAddress.Address
Address = WordAddress.Address
MsgBox ActiveWorkbook.Name & Chr(13) & Address
Range(Address).Activate
GoTo FindAnother
End If
NextBook:
Next i
End Sub

Report •

#7
February 8, 2005 at 09:48:39

Wow, that's lot of work you did.

Thanks so much.

As for the details, I need the date, like mar 17.

That's it.

Basically, each workbook contains a month. Along the bottom are tabs that have each day of the month.(Thanks to Bryan and ASAP Utilites.)

So if an invoice (315747) is in a column called Contract # on mar 17, I need to see that that date contains that contract number.

Once I have that I can navigate to the proper workbook.

BTW, I just tried that and it worked great on the test book.

If I could do that across the workbooks, one for each month, I'd be all set.

And since I would like to learn VBA, where would I start to learn how to do this so I don't have bug someone for the basics?

Larry


Report •

#8
February 8, 2005 at 10:56:43

Any book by John Walkenbach is recommended and there is one called Excel 2000 VBA by John Green that I like a lot. One of the best ways to start learning VBA is to record macros and see what the code looks like. It often needs modification to allow for generalities but it is a good way to learn syntax, etc.

I forgot to search all the sheets in a book in the code I provided. Do you want to attempt it before I give you the answer?


Report •

#9
February 8, 2005 at 12:16:20

Yes, I will.

Thanks so much for the help so far.

Give me a week or two to try and write such a macro and if I get stuck, I'll post with the problem or if by some chance I get it right, I'll let you know.

Larry


Report •

#10
February 8, 2005 at 23:38:05

I'm sure about it : vlookup function goes on closed files. The only prob is that u can make a search on only 1 workboot in a row , i mean u must put a vlookup formula in 1st row to see in January, another to look in feb, and so on. But now u're out of prob i think, my answer is just for fun...

Report •

#11
February 9, 2005 at 04:04:58

Taxi,

I'll look into that also. It could be a good way to do something else I'm working on.

I think I'm going to be very busy for the next few weeks.

Larry

What inspired this silly rhyme?
Two parts vodka, one part lime.


Report •

#12
February 14, 2005 at 05:14:24

On the Excel search, I've decided jus to use the Windows search for right now.

I'm under a time constraint and by the time I learned enough VBA, I'd be past my deadline.

But this has pointed out to me the importance of learning how to code things like this.

Thanks to Grok Lobster nad Taxi for your help.

Larry

BTW, I tried, and I couldn't grok a lobster. Has some luck ith sea turtle once, but no joy with the Lobster.

And unfortunately, I'm old enough to know what "grok" is.



Report •

#13
February 15, 2005 at 09:33:04

Larry,

I haven't tried this, but I think it will work.

Sub SearchBooks()
SearchWord = InputBox("Enter the string to search for")
For i = 1 To Workbooks.Count
Workbooks(i).Activate
For j = 1 to Sheets.Count
Worksheets(j).Activate
Range("A1").Activate
FindAnother:
Set WordAddress = Cells.Find(What:=SearchWord, after:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If WordAddress Is Nothing Then
MsgBox ActiveWorkbook.Name & Chr(13) & "Search string not found"
Else:
If WordAddress.Address = CheckCell Then GoTo NextBook
If ActiveCell.Address = "$A$1" Then CheckCell = WordAddress.Address
Address = WordAddress.Address
MsgBox ActiveWorkbook.Name & Chr(13) & ActiveSheet.Name & Chr(13) & Address
Range(Address).Activate
GoTo FindAnother
End If
Next j
NextBook:
Next i
End Sub

ps - You should not feel unfortunate knowing what grok means


Report •


Ask Question