Another VLookup question.

[Tom Schreiner]

Hi all.
I am using the VLookup function in VBA and can not get it to work. Please help...

Ex.

  |      A       |        B        |

  |______________|_________________|

1 |    1300      |       How       |

  |______________|_________________|

2 |    1518      |       Are       |

  |______________|_________________|

3 |    1614      |       You       |

  |______________|_________________|

4 |    2001      |      Doing      |

  |______________|_________________|

5 |    2124      |      Today      |

  |______________|_________________|

The range A1:B5 is named:
E_List

How do I incorporate this named range into
my VLookup function?

Here is my current code. I keep getting an error.

   

    CurrentDept = "1518"

    

    Set E_AddressRange = ActiveWorkbook.Names("E_List")

    

    E_Address = Application.WorksheetFunction.VLookup(Val(CurrentDept), E_AddressRange, 2)

    

E_Address should return "Are"

Another question:
Can I also get this function to return the row number?
In this example RowNum would equal 2.

Thanks,
Tom

[Dave Patton]

The following UDF (User Defined Function) works. I used named ranges Code and E_List.

Use Function as =LookupA(CODE, E_List)

Function LookupA(CODE, E_List)
If CODE <> "" Then
With Application.WorksheetFunction
LookupA = .VLookup(CODE, E_List, 2, False)
End With
Else
LookupA = ""
End If
End Function

[Tom Schreiner]

Thanks Dave,
Works perfectly...
I was about to give up on this one and just code the formula to a worksheet and use it from there...
Thanks Again!
Tom