vlookup in many books

[Sangel]

\\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

 

[Joel]

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]

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

Thnx Joel

I got the code in, not getting any errors now. But, not getting any
value in return either. cnat 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. donnow maybe im doing something wrong and cant catch it.

 

[Sangel]

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?

 

[Joel]

Stupid mistake.
from
vlookallsheets = vFound
to
vlookallbooks = vFound

The new function isn't returning a value!

 

[Sangel]

Stupid mistake.
from
vlookallsheets = vFound
to
vlookallbooks = vFound

The new function isn't returning a value!

yeah i thought of that and had changed it, still no value is being
looked up. im pretty sure is some simple thing im doing wrong. if you
had workbooka and woorkbookb, how would you use the function in a to
look into b?

 

[Joel]

The "for each wbk in workbooks " statement does it automatically.

 

[Luis A. Vázquez]

The "for each wbk in workbooks " statement does it automatically.

Hi Joel,

ists working now. i found the following:

was like this: For Each wkbk In wkbks

now its like this For Each wkbk In Active.wkbks,

and its working. whats not working is the search in all the sheets. it
works if i specify the workbook and a specific sheet but not all.