Home |
Search |
Today's Posts |
#1
|
|||
|
|||
HELP!! Vlookup
I have this code to look in all sheets, but it stops after it finds the first entry. Im looking through 40+ sheets that could have the same criteria for the lookup. How can I have the first result in a6, then the second in a7, etc... a6 =vlookallsheets($D$4,$H$2:$N$2,1,FALSE) can I have a6 look through sheets 1-40, a7 2-40, a8 3-40, etc... Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _ Col_num As Integer, Optional Range_look As Boolean) Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _ Col_num As Integer, Optional Range_look As Boolean) '''''''''''''''''''''''''''''''''''''''''''''''' 'Written by OzGrid.com '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 -- comotoman ------------------------------------------------------------------------ comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292 View this thread: http://www.excelforum.com/showthread...hreadid=473115 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |