vlookup vba stops when find the first value how to make it continue works and find the next value in same cell

[mido25x]

hi ,


i have vba code for lookup in the entire sheet for the employee number and return back his total working days,

but the employee number can work in 2 places with different total days in different sheets

so the vlookup when it find the the employee number it return back the first total days from sheet and stop it doesn't continue to find the other total days

below is the VBA code

-------------------------------------------------------------------
>>> use code - tags <<<

Function VLOOKUPWORKBOOK( _
    lookup_value As Variant, _
    table_array As Range, _
    col_index_num As Integer, _
    Optional range_lookup As Boolean, _
    Optional sheets_to_exclude_1 As String, _
    Optional sheets_to_exclude_2 As String, _
    Optional sheets_to_exclude_3 As String, _
    Optional sheets_to_exclude_4 As String, _
    Optional sheets_to_exclude_5 As String _
)




Dim mySheet As Worksheet
Dim value_to_return
Dim sheets_to_exclude
Dim CellsEmpty As Boolean
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error Resume Next

sheets_to_exclude = Array(sheets_to_exclude_1, sheets_to_exclude_2, sheets_to_exclude_3, sheets_to_exclude_4, sheets_to_exclude_5)


For Each mySheet In ActiveWorkbook.Worksheets

 

    If Not (UBound(Filter(sheets_to_exclude, mySheet.Name)) > -1) Then

        With mySheet

            Set table_array = .Range(table_array.Address)

            'Run the actual vlookup function on the current sheet that is being looped though.
            value_to_return = WorksheetFunction.VLookup(lookup_value, table_array, col_index_num, range_lookup)
           

        End With

      
        If Not IsEmpty(value_to_return) Then    
           Exit For
        End If

    End If

Next mySheet


VLOOKUPWORKBOOK = value_to_return

End Function


Private Sub Workbook_Open(ByVal Target As Range)

Application.Iteration = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[vletm]

mido25x
You have already once noted that do not duplicate Your thread.
Continue with You previous same thread
and
use code tags, if You paste Your code here.
This thread is closed.