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
Of course yes, u've only got an advertisin before update.
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
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.
Grok Lobster: Ha! Me write a macro.
That's funny.OK, I'll try. Any leads on where I could look for instructions?
Larry
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 SubOpen 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?
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
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
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?
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
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...
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.
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.
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 Subps - You should not feel unfortunate knowing what grok means
