On Oct 8, 9:57 pm, Sangel wrote:
On Oct 8, 8:46 pm, Joel wrote:



Here we go again.

Function vlookallbooks(Look_Value As Variant, Tble_Array As Range, _
Col_num As Integer, Optional Range_look As
Boolean)

'Use VLOOKUP to Look across ALL Worksheets and stops _
at the first match found.
'''''''''''''''''''''''''''''''''''''''''''''''''
Dim wSheet As Worksheet
Dim vFound

On Error Resume Next

for each wbk in workbooks
For Each wSheet In wbk.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 Exit For
Next wSheet
If Not IsEmpty(vFound) Then Exit For
next wbk
Set Tble_Array = Nothing
vlookallsheets = vFound

End Function

"Sangel" wrote:
\\hi guys,

\\i have this piece of code to make vlookup work thru a work book. I'm
trying to make it go thru different work books. let say i have 2 o 3
workbooks open, i wan to to be able to tell a cell:

vlookallbooks(j2,c:h,2,false) and for it to look in that range of all
open excel books.

\\CODE
Function vlookallsheets(Look_Value As Variant, Tble_Array As Range, _
Col_num As Integer, Optional Range_look As
Boolean)

'Use VLOOKUP to Look across ALL Worksheets and stops _
at the first match found.
'''''''''''''''''''''''''''''''''''''''''''''''''
Dim wSheet As Worksheet
Dim vFound

On Error Resume Next

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 Exit For
Next wSheet
Set Tble_Array = Nothing
vlookallsheets = vFound

End Function

\\any suggestions ???

thnx

Thnx Joel

I got the code in, not getting any errors now. But, not getting any
value in return either. can't get to point the finger on what it could
be. Is it correct for me to be using the formula as i wrote above in
the cell. don't now maybe i'm doing something wrong and cant catch it.

cant find what i'm doing wrong. to use the function i'm entering it
like this in a cell:

=vlookallbooks(j2,c:h,2,false)

this works well when i'm using it with the code for vlookallsheets,
but not getting anything with vlookallbooks. i've also tried including
the direct name of the file. no luck.

any thoughts?