Function ControlActivityLookup(ByVal str As String) As String
Dim CA As Variant
Dim rngCA As Range
Dim strTemp As Variant
Dim i As Integer
Dim Worksheet As String

Worksheet = "Sheet2"
CA = Split(str, ",")

Set rngCA = Range(Worksheet & "!" & "$a$1:$b$5")

i = 0

Do While i <= UBound(CA)
strTemp = strTemp & ". " & _
Application.WorksheetFunction. _
VLookup(CA(i), rngCA, 2)
i = i + 1
Loop

ControlActivityLookup = Trim(CStr(strTemp))

End Function

From the immediate window:

? ControlActivityLookup("A,B")
.. 1. 2


where the table in Sheet2 was

A 1
B 2
C 3
D 4
E 5

--
Regards,
Tom Ogilvy


"StylinEric " wrote in message
...
I am trying to insert values into an adjacent cell based upon lookup
values entered. For example if I enter A,B in cell C1, I want it to go
look up the corresponding value of what A and B are and enter them into
the next cell. What I've come up with is a function that I will call
in the adjacent cell passing it the values from the previous cell.

Problem: I'm either having a hard time because I am calling the range
wrong or I am doing something wrong when doing the Vlookup. (or both)
:)

I am calling the function from sheet1, the vlookup table is in sheet 2
column a has the value I'm passing, column b has the value I want the
function to return

Private Function ControlActivityLookup(ByVal str As String) As String
Dim CA() As String
Dim rngCA As Range
Dim strTemp As Variant
Dim i As Integer
Dim Worksheet As String

Worksheet = "Sheet2"
CA() = Split(str, ",")

rngCA = Range(Worksheet & "!" & "$a$1:$b$5")

i = 0

Do Until i = (UBound(CA()) + 1)
strTemp = strTemp & ". " &
Application.WorksheetFunction.VLookup(CA(i), rngCA, 2)
i = i + 1
Loop

ControlActivityLookup = Trim(CStr(strTemp))

End Function

Anyone with any help, even if its just steering me in the right
direction woudl be much appreciated. I've tried doing just
application.vlookup with the same error. The function completes and
returns #name before finishing one loop cycle


---
Message posted from http://www.ExcelForum.com/