I have been using the vlookupworkbook successfully for 6 months in the same workbook looking up teh same data. a couple of weeks ago the formula just started returning #Value!. Error checker looks like the workbook funtion is no longer recognized. The code has not changed nor the formula. Workbook has 350 sheets, any suggestions to resolve this woudl be appreciated.
<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 _
)
'------------------------------------------------'
'------------------------------------------------'
' TeachExcel.com
'------------------------------------------------'
'------------------------------------------------'
Dim mySheet As Worksheet
Dim value_to_return
Dim sheets_to_exclude
On Error Resume Next
'Put sheets to exclude into an array
sheets_to_exclude = Array(sheets_to_exclude_1, sheets_to_exclude_2, sheets_to_exclude_3, sheets_to_exclude_4, sheets_to_exclude_5)
'Cycle through all sheets in the workbook
For Each mySheet In ActiveWorkbook.Worksheets
'Check if the sheet needs to be excluded or not.
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
'Exit the loop once have a value to return.
If Not IsEmpty(value_to_return) Then
Exit For
End If
End If
Next mySheet
'Send the result back to the cell that contains the function.
VLOOKUPWORKBOOK = value_to_return
End Function>
here is the formula I use:
=IF(VLOOKUPWORKBOOK(V321,B:B,1,FALSE,"Order Check","Issues Log")=V321,"Yes","NO")
result now:
#VALUE!