Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in many books
\\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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in many books
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" wrote: \\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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in many books
On Oct 8, 8:46 pm, Joel wrote:
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" wrote: \\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 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in many books
On Oct 8, 9:57 pm, Sangel wrote:
On Oct 8, 8:46 pm, Joel wrote: 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" wrote: \\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 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in many books
Stupid mistake.
from vlookallsheets = vFound to vlookallbooks = vFound The new function isn't returning a value! "Sangel" wrote: On Oct 8, 9:57 pm, Sangel wrote: On Oct 8, 8:46 pm, Joel wrote: 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" wrote: \\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 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in many books
On Oct 9, 11:37 am, Joel wrote:
Stupid mistake. from vlookallsheets = vFound to vlookallbooks = vFound The new function isn't returning a value! "Sangel" wrote: On Oct 8, 9:57 pm, Sangel wrote: On Oct 8, 8:46 pm, Joel wrote: 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" wrote: \\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 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? 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in many books
The "for each wbk in workbooks " statement does it automatically.
"Sangel" wrote: On Oct 9, 11:37 am, Joel wrote: Stupid mistake. from vlookallsheets = vFound to vlookallbooks = vFound The new function isn't returning a value! "Sangel" wrote: On Oct 8, 9:57 pm, Sangel wrote: On Oct 8, 8:46 pm, Joel wrote: 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" wrote: \\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 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? 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in many books
On Oct 9, 2:03 pm, Joel wrote:
The "for each wbk in workbooks " statement does it automatically. "Sangel" wrote: On Oct 9, 11:37 am, Joel wrote: Stupid mistake. from vlookallsheets = vFound to vlookallbooks = vFound The new function isn't returning a value! "Sangel" wrote: On Oct 8, 9:57 pm, Sangel wrote: On Oct 8, 8:46 pm, Joel wrote: 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" wrote: \\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 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? 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? 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup thru many books | Excel Programming | |||
which books..? | New Users to Excel | |||
VBA-Books | Excel Programming | |||
Books | Excel Programming | |||
Books | Excel Programming |