• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Function vlookallbooks

K

kdsim

New Member
Hi everyone,

I'm new here and am hoping you can help as I've been over and over this and cannot figure out why it's not working. I'm am VERY NEW to VBA so it could be as simple as placing the code in the wrong module or not "linking" it to the worksheet properly which is why I'm reaching out to all you experts! :)


I have a worksheet that I enter a part number in and then I would like that worksheet to lookup that number in another worksheet that contains about 70 tabs for a descpription. To that end I was able to find this code but, when I enter this formula: =vlookallbooks("AI039301",A:C,2,FALSE) I get a #NAME? error; I've tried entering the name of the other worksheet too but still no luck.


I would appreciate any help you can provide!! Thanks.

Here's the code I'm using:

[pre]
Code: 
Function vlookallbooks(Look_Value As Variant, Tble_Array As Range, Col_num As Integer, Range_look As Boolean, ParamArray wkbks())

'Use VLOOKUP to Look across ALL Worksheets and stops at the first match found.

Dim wSheet As Worksheet
Dim vFound

On Error Resume Next
found = False
For Each wkbk In Active.wkbks
Set Searchbook = Workbooks(wkbk)
For Each wSheet In ActiveWorkbook.Worksheets
With wSheet
Set Tble_Array = .Range(Tble_Array.Address)
vFound = WorksheetFunction.VLookup Look_Value, Tble_Array, Col_num, Range_look)
End With
If Not IsEmpty(vFound) Then
found = True
Exit For
End If
Next wSheet
If found = True Then Exit For
Next wkbk
Set Tble_Array = Nothing
vlookallbooks = vFound

End Function
[/pre]
 
Are you sure the above code is stored in a generic module in the VBE, or is it in a sheet module? Since it's a function, it needs to be in a generic one.
 
Hi Luke,


Thanks for the response...... let's see, in the pane on the left it's in a module in the appropriate file, I've renamed the module to vlookallbooks. In the right pane, at the top in the first drop down area it says (General), in the second drop down it says vlookallbooks.


Does that answer your question?
 
Hmm, sounds right. IMO, I wouldn't give the module the "exact" same name as the function, as it could cause confusion later, but it shouldn't affect the formula. My answer was prompted because the #NAME? error indicates that XL doesn't recognize the function.


Here's another test we can do. In a blank cell in your workbook, click the Fx button to get the "Insert Function" dialogue. Then, select the "User defined" category. Scroll through the list to see if the vlookallbooks function is available. If it is, the problem is with the formula syntax. If not, it's a problem with code. Can you check and let us know which it is?
 
Vlookallbooks is available so I guess it's a formula syntax problem.


I selected it and entered info and now I got a 0 instead of the #NAME? error; I'm not sure why though.


I'm headed to lunch --- I'll be back in about a 1/2 hour.


Thanks!
 
After doing some test, I discovered that the problem is due to the module being the same name as function. Since they're the same name, XL is having trouble knowing whether you are referring to the function or module when you write it yourself. The problem disappears when you use the Insert Function dialogue, since XL knows you want a function in that case.


So, as a solution, change the name of the module (add some extra chracters maybe, or call it "modVlookallbooks"). That will solve any future #NAME? errors. Now, just need to get the syntax right. =)
 
Ok, I changed the name of the module and that seemed to work for one issue as when I hard enter the vlookallbooks formula I do get a 0 instead of an error. However, it seems like it's only looking at the current workbook and current worksheet, which is certainly not what I need it to do! LOL...... Any thoughts on the syntax?
 
Hmm. Do you need it to look at multiple workbooks, or just multiple worksheets? Testing the function on my machine, it's working okay. Now, there was a VB error in the code you posted above for this line:

Code: 
vFound = WorksheetFunction.VLookup Look_Value, Tble_Array, Col_num, Range_look)

as it's missing a parenthesis. Should be:

vFound = WorksheetFunction.VLookup(Look_Value, Tble_Array, Col_num, Range_look)


but I'm guessing it was just a copy error. For your syntax, the first argument is the value you are looking for. So, if you want to search every worksheet's col B for "cat", and return corresponding value from col D, formula would be:

=vlookallbooks("cat",B:D,3,FALSE)


Does that help?
 
Thanks again for the response.....


I need to look at multiple workbooks across multiple sheets; does that make a difference in the code?


You were correct that the missing ( was a copy/paste error.


I guess I had the syntax correct as I was doing exactly what you spelled out above: =vlookallbooks("AI039301",A:C,2,FALSE) but I still get a 0 when I enter the formula.


I don't understand why it's not working for me.
 
referencing this discussion:

http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.programming/2007-10/msg01106.html


Looks like the code you originally had needs some other tweaks so that it actually looks in other workbooks. I've tested this on my machine and it works ok.

[pre]
Code: 
Function vlookallbooks(Look_Value As Variant, Tble_Array As Range, Col_num As Integer, Range_look As Boolean, ParamArray wkbks())

'Use VLOOKUP to Look across ALL Worksheets and stops at the first match found.

Dim wSheet As Worksheet
Dim vFound
Dim searchBook As Workbook
Dim found As Boolean

On Error Resume Next
found = False
For Each wkbk In wkbks
Set searchBook = Workbooks(wkbk)
For Each wSheet In searchBook.Worksheets
With wSheet
Set Tble_Array = .Range(Tble_Array.Address)
vFound = WorksheetFunction.VLookup(Look_Value, Tble_Array, Col_num, Range_look)
End With
If Not IsEmpty(vFound) Then
found = True
Exit For
End If
Next wSheet
If found = True Then Exit For
Next wkbk
Set Tble_Array = Nothing
vlookallbooks = vFound

End Function
[/pre]
Example call:

=vlookallbooks( "Joel", A1:C5,2,FALSE, "book2.xls","Book1.xlsx","My book.xlsm")
 
Ok, I copied and pasted your code above and entered the following forumla:


=vlookallbooks(G2,A:C,2,FALSE,"Item Number Log.xls")

the item I'm looking up is housed in G2 and I want to look at all sheets of the item number log in column A. The result will come from column b and then, once this is working, column c as well. I even entered the information as "AI039301" instead of G2 but that didn't do anything either.


I still get a 0 ..... this is quite frustrating since it's working for you.... I'm really at a loss about what to do next?! :)
 
I tried it again by using the FX instead of typing it in and I get the following VB message: User-defined type not defined; what does that mean?
 
Me again.... ok, so I got the error I just told you about but.... it did find the data ..... how weird is that? I don't want to get that error all the time though ..... any thoughts?!
 
Ok, so I closed the file, reopened, and entered the formula multiple times and it seems to be working ....... I guess the old turn it off and on again worked!! LOL


I cannot thank you enough for all your help with this!!! I'll be posting again I'm sure! LOL


Have a fantastic evening!
 
Uh-oh ..... *totally red here* ..... I told you I'd be posting again...... LOL I just didn't expect to do it so soon! YIKES....


I just closed both of the files and was playing around to see exactly how well this would work. I knew that the item # log would have to be open but I didn't realize that you had to open it with the password; I was hoping that someone could open the file as a read only and the forumla would work but........ alas, it doesn't seem so.


Any thoughts? Thanks again (or still :))!
 
If the password is required to open the file, that's going to be difficult to overcome. It's possible to read from closed files, but you need to at least be able to prove that you have read access. =(


If the information is needed, perhaps the argument/case should be made to have the info moved to a read-only location?


As for the issue(s) with all the errors, I'm not exactly sure what the cause/solution was, but at least it sounds like you got it working. My guess is that it has something to do with names (of module and/or function) and the name of workbooks you type.
 
Hi Luke,

I opened both files later in the day; the item # log as a read only and what I found is that the fields on the shop order log that had the vlookallbooks formula didn't automatically populate BUT, if I entered the cell and went to the end of the formula and hit enter it did populate so, I assume it's like some kind of refresh..... does that sound possible?
 
Add this line right after the Dim statements in your function

Code: 
Application.Volatile


This tells XL to recalculate the function whenever there is a change. Note that you will take a small performance hit, since this could potentially cause unnecessary calculations to occur, but, on the other hand, the data should always be up to date.
 
Hi Luke,

Thanks again, I think that will work but it really does cause performance issues so I'm opting not to use it ..... I'm putting a link to the other spreadsheet & telling people to open it before entering the item #.


I really appreciate all your help!! I'm sure we'll "speak" again :).


Take care
 
Sounds good. One last though, before you leave. If the formulas aren't refreshing (and you don't want to set them to volatile), you can manually force a recalc by hitting Ctrl+Alt+F9. Depending on your situation, that might work.


Have a good one!
 
Thanks Luke, I'll try the Ctrl+Alt+F9 thing but now I have yet another question! LOL


I realized that the Vlookallbooks would be very useful in other spreadsheets that I have; all of them looking to the item number log, but when I try to use it I have to use the FX & select from the UDF in the shop order log (only when I have it open).


Is there a way to make the code available to all my worksheets?
 
worksheets or workbooks? If the UDF is stored in a generic module (as it should), it should be available on any worksheet within that workbook. (I'm assuming the module name issue has been cleared up). If you want the UDF to be accessed by multiple workbooks, you'll need to either install it as an add-in, or store it in your Personal.xls workbook. This site can give you guidance on how to set such a thing up:

http://dmcritchie.mvps.org/excel/getstarted.htm
 
I did mean workbooks........ yes the module name has been changed and I did store it in my Personal.xls workbook but it doesn't seem to be working correctly.


Hmm.... I'll play around with it and peruse the site you linked to and I'll keep you abreast of my progress.


Thanks again for all your help; I'll be leaving shortly for the weekend so I'll be in touch sometime next week!


Thanks.... have a great weekend!
 
You must log in or register to reply here.
Back
Top