I copied your function from the email, and pasted it into a GENERAL module.
(not a worksheet module)

in the worksheet itself I put in

C4: A,B

In D4 i put in

=ControlActivityLookup(C4)

worked fine for me.

--
Regards,
Tom Ogilvy

"StylinEric " wrote in message
...
Thanks for getting back to me so quickly. However I am still having
difficulties with getting the correct value. I still get #value! to
return after running the fucntion.

Right before the vlookup in the intermediate window I get:

Unable to get Vlookup property of worksheet function class

------------------------------------------------

Does it matter that I am using 2002. Here is the code 'm using, all I
changed was the worksheet name as I am referencing sheet1 from a
different sheet labled PD. I wonder why you got it to work, but I'm
not getting it. Same code ran in the cell, next to the cell that is
passing the info

I get str = 1,2,3,4

I still think there is something wrong with the range being called or
the way vlookup is being performed


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 = "Sheet1"
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

----------------------------------------------------------------------

Thanks agian for the help! ;)


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