vlookup function

[Felicity Geronimo]

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

 

[Don Guillett]

Look in vba HELP for FINDNEXT to see a good example of a better way.

 

[KL]

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("B110")
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("B110", "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]

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("B110")
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("B110", "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

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

 

[Felicity Geronimo]

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("B110")
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("B110", "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

Hi,

Ive tried your code but it doesnt do what i need it to really, i have
several sheets that contain something like the following:

Sheet1/Sheet2
NAME SITE DEPT
Rachel Manchester IT
Matt Crewe HR
Ian Bury Finance
Steve Preston HR
Dan Crewe Finance

I also have a summary sheet that contains the named range SITE, this
has a list of all possible sites each employee can be in, i want to
look up each record one after the other (in sheet 1/2 etc)and see
whether their site exists in the named range, if it does exist move
onto the next cell down if it doesnt exist display a message bos
explaining that the site is not in the named range.

Summary sheet looks something like:

Crewe
Bury
Warton
Exeter

So if we use the above example the code would stop once it got to
record number 4 because Preston has not been added to the named range.

One other problem is that the amount of records will always change so
their is no way of knowing how many rows in each sheets to be checked.
I have tried using a do until but i dont seem to be able to insert a
vlookup aswell.

Please help

Flick. x

 

[KL]

Would this do it?
Regards,
KL

Sub FindNonListedSites()
Dim mySheets, i
Dim MyTable As Range
Dim ws As Integer
Dim c As Range

mySheets = Array("Sheet1", "Sheet2", "Sheet3")
Set MyTable = Sheets("Sheet4").Range("SITE")

For ws = 0 To UBound(mySheets)
Set rng = Worksheets(mySheets(ws)).UsedRange.Columns(2).Cells
For Each c In rng
If c <> "SITE" And c <> "" Then
With MyTable
Set i = .Find(c.Value, LookIn:=xlValues)
If i Is Nothing Then
MsgBox c.Value & " - not in list"
Exit Sub
End If
End With
End If
Next c
Next ws
End Sub