Excel VBA - VLookup problem referencing another sheet in the same workbook

[StylinEric]

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

 

[Tom Ogilvy]

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

 

[StylinEric]

Thanks for getting back to me so quickly. However I am still havin
difficulties with getting the correct value. I still get #value! t
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
changed was the worksheet name as I am referencing sheet1 from
different sheet labled PD. I wonder why you got it to work, but I'
not getting it. Same code ran in the cell, next to the cell that i
passing the info

I get str = 1,2,3,4

I still think there is something wrong with the range being called o
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

 

[Tom Ogilvy]

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.

 

[StylinEric]

How strange, I'm doing the same thing, I've been running it in a genera
module. I keep getting the same error

unable to get vlookup property of worksheet function class (i
intermediate window) in addition after is passes by the range, I can'
type ? rngCA without an error.

At this point, I'm not sure how else to got about it. If it seems t
be working fine for you, thats strange. I still think there i
something going on with the range. Even if I pass the range as
string rather than a range I still get the same error. I've even trie
naming the range on the other sheet to no avail.



Thanks for all your help though Tom

 

[StylinEric]

I got it!!

I tried changing my lookup values to A and B rather than looking for 1
2 as it was doing. for some reason changing it to letters and lette
is the lookup worked. It must be some type conversion error

thanks for all the help Tom, it got me thinking. I think I should hav
been more clear on my original post actually saying 1, 2 rather tha
a,b

Thanks agian,

Now I'm off to solve the type conversion proble

 

[StylinEric]

purring a cint infront of CA(i) in the vlookup fixed the problem

thanks agian Tom

 

[Tom Ogilvy]

split doesn't convert the string values you pass to it into numbers, so
doing the conversion, as you have discovered, is required (since the data in
the lookup table are apparently numbers).