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
:
On 05 October 2007 14:28
Sangel wrote:
Re: vlookallsheets across workbooks
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",...)
:
On 05 October 2007 14:53
Sangel wrote:
Re: 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
:
On 07 October 2007 10:21
Sangel wrote:
Re: 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
:
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/tutorial...90ab-50e54bcbc010/aspnet-applicationpage.aspx