vlookallsheets across workbooks
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
|