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 <<<
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 <<<
Code:
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