This line is just a sample of the call to the function. Not sure if you are
calling from VBA or worksheet. Dots was my lazy way to show all the old
parametrs. New workbook names are strings.

Don't se this line. It was to show that workbook names are strings with xls
included. workbooks need to ber opended.
=vlookallbooks(........,"book1.xls","book2.xls",.. .)


"Sangel" wrote:

On Oct 5, 3:38 am, Joel wrote:
Try this

I changed the name of the function and made tble_Array a non-optional
parameter. Then added wkbks. wkbks is a string and can be multiple
workbooks. I did not test changes, but it should work if I didn't make any
typos.

=vlookallbooks(........,"book1.xls","book2.xls",.. .)

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 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
Next wSheet
if found = true then exit for
next wkbk

Set Tble_Array = Nothing
vlookallsheets = vFound

End Function

"Sangel" wrote:
Hi guys,

i recieved this 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

that enables vlookup to search across the whole workbook. How can i
make vlookup to search across two diferent workbook?

can it be done?
thnx

Thnx Joel

Im gettin a :

Compile Error
Expected line or lable or statement or end of statement. And the
following part of the code is red.

=vlookallbooks(........,"book1.xls","book2.xls",.. .)

Function vlookallbooks(Look_Value As Variant, Tble_Array As Range, _
Col_num As Integer, Range_look AsBoolean,
ParamArray wkbks())

What do you think can be going on?

Hope to hear from you