Question on vba that i found for doing a vlookup across multiple sheets

[rogeryung]

Hi, I'm trying to do a vlookup across multiple sheets and found the macro listed at the bottom. It works, but it's doing a lookup across all sheets. Can anyone tell me how to change it so that it only looks at a specific set of sheets? Basically I want it to look at all sheets between a sheet called "Start" and a sheet called "End"

Thank you

Here it is:

Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, Col_num As Integer, Optional Range_look As Boolean)








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

 

[Jonmo1]

Try

Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, Col_num As Integer, Optional Range_look As Boolean)
Dim wSheet As Worksheet, i As Long, vFound
On Error Resume Next
For i = Sheets("Start").Index To Sheets("End").Index
    Set wSheet = Sheets(i)
    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 i
Set Tble_Array = Nothing
VLOOKAllSheets = vFound
End Function

 

[rogeryung]

that worked perfectly. Thank you so much!

 

[Jonmo1]

You're welcome.