I am trying to insert values into an adjacent cell based upon looku
values entered. For example if I enter A,B in cell C1, I want it to g
look up the corresponding value of what A and B are and enter them int
the next cell. What I've come up with is a function that I will cal
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 rang
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 th
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 righ
direction woudl be much appreciated. I've tried doing jus
application.vlookup with the same error. The function completes an
returns #name before finishing one loop cycl

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