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