I want to start by saying I'm very new to macros so this may be a stupid question. I tried to run this macro here
Function VLOOKUPWORKBOOK1(Look_Value As Variant, Tble_Array As Range, Col_num As Integer, Optional Range_look As Boolean)
'Vlookup function that will search all worksheets in the workbook - however, the data table that is being searched must be in the same
'location on every worksheet.
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
VLOOKUPWORKBOOK = vFound
End Function
But when I try to use it, I get the error: Compile error: Argument not optional
I have attached the workbook I'm trying to do this in. The command I use is =VLOOKUPWORKBOOK1(A3, 'TS-5D1'!D3:D103, 4, 0) and is found on the Materials List tab in cell D3.
Any help you could give would be great!
Bookmarks