Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - VLookup problem referencing another sheet in the same workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - VLookup problem referencing another sheet in the same workbook
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - VLookup problem referencing another sheet in the same workbook
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 ---------------------------------------------------------------------- Thanks agian for the help! ; -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - VLookup problem referencing another sheet in the same workbook
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - VLookup problem referencing another sheet in the same workbook
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 -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - VLookup problem referencing another sheet in the same workbook
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 -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - VLookup problem referencing another sheet in the same workbook
purring a cint infront of CA(i) in the vlookup fixed the problem
thanks agian Tom -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - VLookup problem referencing another sheet in the same workbook
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). -- Regards, Tom Ogilvy "StylinEric " wrote in message ... purring a cint infront of CA(i) in the vlookup fixed the problem thanks agian Tom! --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing another workbook using a variable for the sheet? | Excel Worksheet Functions | |||
VLOOKUP - Referencing seperate workbook getting #N/A erros | Excel Worksheet Functions | |||
workbook referencing problem | Excel Worksheet Functions | |||
Problem w/formula referencing another sheet | Excel Worksheet Functions | |||
VLOOKUP referencing another workbook | Excel Discussion (Misc queries) |