Vlookup VB Help

[comotoman]

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]

Anybody got any ideas