Hello all,
I've got a workbook that has roughly 60 sheets, with ID numbers in column A on every sheet. I am trying to write a public function in VBA that will essentially extend the VLOOKUP function so that it will return the first value that corresponds to the lookup value in whatever sheet has the ID number. Here is the code I have so far:
Public Function supervlookup(refrange As Range, rangename As Range, columnnumber As Integer)
Dim sh As Worksheet
For Each sh In Worksheets
If Not Application.WorksheetFunction.IsErr(Application.WorksheetFunction.VLookup(refrange, rangename, columnnumber, "false")) Then
supervlookup = Application.WorksheetFunction.VLookup(refrange, rangename, columnnumber, "false")
Exit Function
Else
End If
Next sh
End Function
The function thusfar works if the ID being searched for is on the same page as the formula, but it returns a #NUM error if the ID is on another sheet. Any help would be greatly appreciated!
I've got a workbook that has roughly 60 sheets, with ID numbers in column A on every sheet. I am trying to write a public function in VBA that will essentially extend the VLOOKUP function so that it will return the first value that corresponds to the lookup value in whatever sheet has the ID number. Here is the code I have so far:
Public Function supervlookup(refrange As Range, rangename As Range, columnnumber As Integer)
Dim sh As Worksheet
For Each sh In Worksheets
If Not Application.WorksheetFunction.IsErr(Application.WorksheetFunction.VLookup(refrange, rangename, columnnumber, "false")) Then
supervlookup = Application.WorksheetFunction.VLookup(refrange, rangename, columnnumber, "false")
Exit Function
Else
End If
Next sh
End Function
The function thusfar works if the ID being searched for is on the same page as the formula, but it returns a #NUM error if the ID is on another sheet. Any help would be greatly appreciated!
