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

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

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

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

On Oct 5, 2:41 pm, Joel wrote:
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

Great i got that part going, Now it gives me a NEXT without FOR
error.

The end if is missing shown below

Exit For
End If
Next wSheet


"Sangel" wrote:

On Oct 5, 2:41 pm, Joel wrote:
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

Great i got that part going, Now it gives me a NEXT without FOR
error.

On Oct 5, 3:26 pm, Joel wrote:
The end if is missing shown below

Exit For
End If
Next wSheet

"Sangel" wrote:
On Oct 5, 2:41 pm, Joel wrote:
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

Great i got that part going, Now it gives me a NEXT without FOR
error.

Checked it again, and its giving me an error now at this point:

Set Tble_Array = Nothing
vlookallsheets = vFound

Hope you can help.

Here is the code again. I only changed the reurn line because the name of
the function was change from vlookallsheets to vlookallbooks. I tested the
code and it seemed to work. Here is the formula I used to test it. It can
also be called from another VBA macro (without the equal sign).

=vlookallbooks( "Joel", A1:C5,2,FALSE, "c:\temp\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
End If
Next wSheet
If found = True Then Exit For
Next wkbk

Set Tble_Array = Nothing
vlookallbooks = vFound

End Function


"Sangel" wrote:

On Oct 5, 3:26 pm, Joel wrote:
The end if is missing shown below

Exit For
End If
Next wSheet

"Sangel" wrote:
On Oct 5, 2:41 pm, Joel wrote:
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

Great i got that part going, Now it gives me a NEXT without FOR
error.

Checked it again, and its giving me an error now at this point:

Set Tble_Array = Nothing
vlookallsheets = vFound

Hope you can help.

Hi,

I dont know much about macros but i am trying to do a vlookup with another workbook using 4 tables and was wondering if someone can give me a VBA code to do it

Regards
Rhoma



Sangel wrote:

vlookallsheets across workbooks
04-Oct-07

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

Previous Posts In This Thread:

On 04 October 2007 18:08
Sangel wrote:

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

On 05 October 2007 03:38
Joe wrote:

Try thisI changed the name of the function and made tble_Array a non-optional
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:

On 05 October 2007 14:28
Sangel wrote:

vlookallsheets across workbooks
On Oct 5, 3:38 am, Joel wrote:

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

On 05 October 2007 14:41
Joe wrote:

This line is just a sample of the call to the function.
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 05 October 2007 14:53
Sangel wrote:

vlookallsheets across workbooks
Great i got that part going, Now it gives me a NEXT without FOR
error.

On 05 October 2007 15:26
Joe wrote:

The end if is missing shown below Exit For End If Next
The end if is missing shown below

Exit For
End If
Next wSheet


"Sangel" wrote:

On 07 October 2007 10:21
Sangel wrote:

vlookallsheets across workbooks
Checked it again, and its giving me an error now at this point:

Set Tble_Array = Nothing
vlookallsheets = vFound

Hope you can help.

On 07 October 2007 16:52
Joe wrote:

Here is the code again.
Here is the code again. I only changed the reurn line because the name of
the function was change from vlookallsheets to vlookallbooks. I tested the
code and it seemed to work. Here is the formula I used to test it. It can
also be called from another VBA macro (without the equal sign).

=vlookallbooks( "Joel", A1:C5,2,FALSE, "c:\temp\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
End If
Next wSheet
If found = True Then Exit For
Next wkbk

Set Tble_Array = Nothing
vlookallbooks = vFound

End Function


"Sangel" wrote:

EggHeadCafe - Software Developer Portal of Choice
..NET 2.0 Generics - Load A Business Class In A Clueless Database Layer
http://www.eggheadcafe.com/tutorials...ics--load.aspx

Hi,

I dont know much about macros but i am trying to do a vlookup with another workbook using 4 tables and was wondering if someone can give me a VBA code to do it

Regards
Rhoma



Sangel wrote:

vlookallsheets across workbooks
04-Oct-07

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

Previous Posts In This Thread:

On 04 October 2007 18:08
Sangel wrote:

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

On 05 October 2007 03:38
Joe wrote:

Try thisI changed the name of the function and made tble_Array a non-optional
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:

On 05 October 2007 14:28
Sangel wrote:

vlookallsheets across workbooks
On Oct 5, 3:38 am, Joel wrote:

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

On 05 October 2007 14:41
Joe wrote:

This line is just a sample of the call to the function.
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 05 October 2007 14:53
Sangel wrote:

vlookallsheets across workbooks
Great i got that part going, Now it gives me a NEXT without FOR
error.

On 05 October 2007 15:26
Joe wrote:

The end if is missing shown below Exit For End If Next
The end if is missing shown below

Exit For
End If
Next wSheet


"Sangel" wrote:

On 07 October 2007 10:21
Sangel wrote:

vlookallsheets across workbooks
Checked it again, and its giving me an error now at this point:

Set Tble_Array = Nothing
vlookallsheets = vFound

Hope you can help.

On 07 October 2007 16:52
Joe wrote:

Here is the code again.
Here is the code again. I only changed the reurn line because the name of
the function was change from vlookallsheets to vlookallbooks. I tested the
code and it seemed to work. Here is the formula I used to test it. It can
also be called from another VBA macro (without the equal sign).

=vlookallbooks( "Joel", A1:C5,2,FALSE, "c:\temp\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
End If
Next wSheet
If found = True Then Exit For
Next wkbk

Set Tble_Array = Nothing
vlookallbooks = vFound

End Function


"Sangel" wrote:

On 13 November 2009 09:34
Rhoma Erekpaine wrote:

vlookup across workbook- VBA code
Hi,

I dont know much about macros but i am trying to do a vlookup with another workbook using 4 tables and was wondering if someone can give me a VBA code to do it

Regards
Rhoma

EggHeadCafe - Software Developer Portal of Choice
ASP.NET Application-Page Lifecycle Redux
http://www.eggheadcafe.com/tutorials...ationpage.aspx