ExcelBanter - View Single Post

Felicity Geronimo

"KL" <lapink2000(at)hotmail.com (former ) wrote in message ...
It is possible...:

Function MYVLOOKUP(myValue, myTable As Range)
MYVLOOKUP = WorksheetFunction.VLookup(myValue, myTable, 2, False)
End Function

...but it makes no sense as it is a duplication of the existing function.
Rather you could do something like this:

Sub test()
Dim myTable As Range, c As Range
Set myTable = Range("B1:D10")
For Each c In Range("A1:A10")
On Error Resume Next
myValue = WorksheetFunction.VLookup(c.Value, myTable, 2, False)
If Not myValue Is Error Then
If c.Value < "" Then 'your code here
End If
Next c
End Sub

or if you want to scan multiple tables, then something like this:

Sub test()
Dim myTable
Dim i As Integer
Dim c As Range
myTable = Array("B1:D10", "E1:G10", "H1:J10")
For i = 0 To 2
For Each c In Range("A1:A10")
On Error Resume Next
myValue = WorksheetFunction.VLookup(c.Value, Range(myTable(i)),
2, False)
If Not myValue Is Error Then
If c.Value < "" Then 'your code here
End If
Next c
Next i
End Sub

Regards,
KL
"Felicity Geronimo" wrote in message
m...
Hi,

Is it possible to pass two arguments to a function and then do a
vlookup with the two arguments. One arg would be a cell and the second
arg would be a worksheet named range.

I want to work down a particular column selcecting each cell in turn,
with each active cell i want to lookup that value and check whether it
exists in a named range on a different sheet.

Any ideas please?

Felicity

Thank you for your reply, the problem is, is that the