Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using vLookup function in VBA
I'm an Access programmer, venturing into Excel VBA.
I have a form that contains a text value ("1.1.6") that is one of many values in my TaskInfo Worksheet. I want to use Vlookup to get the values in the 2nd, 3rd, and 4th columns of this worksheet, but cannot seem to make it work. My code segment looks like: Dim lookupRange as range set lookupRange = Worksheets("TaskInfo").Range("A2:A200") me.text2.Value = application.worksheetfunction.vlookup("1.1.6", lookupRange, 2) But this generates a runtime error 1004: Unable to get the Vlookup property of the WorksheetFunction class When set the lookupRange in the Immediate window, I am able to debug.print lookupRange(1), etc, so I know the range is defined properly, but have know idea why I am getting this error message. Any help would be greatly appreciated. -- Email address is not valid. Please reply to newsgroup only. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using vLookup function in VBA
You are looking for the value in the second column of a range that is only 1
column wide. Dim lookupRange as range set lookupRange = Worksheets("TaskInfo").Range("A2:B200") 'B200 not A me.text2.Value = application.worksheetfunction.vlookup("1.1.6", lookupRange, 2) -- HTH... Jim Thomlinson "Dale Fye" wrote: I'm an Access programmer, venturing into Excel VBA. I have a form that contains a text value ("1.1.6") that is one of many values in my TaskInfo Worksheet. I want to use Vlookup to get the values in the 2nd, 3rd, and 4th columns of this worksheet, but cannot seem to make it work. My code segment looks like: Dim lookupRange as range set lookupRange = Worksheets("TaskInfo").Range("A2:A200") me.text2.Value = application.worksheetfunction.vlookup("1.1.6", lookupRange, 2) But this generates a runtime error 1004: Unable to get the Vlookup property of the WorksheetFunction class When set the lookupRange in the Immediate window, I am able to debug.print lookupRange(1), etc, so I know the range is defined properly, but have know idea why I am getting this error message. Any help would be greatly appreciated. -- Email address is not valid. Please reply to newsgroup only. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using vLookup function in VBA
The VLookup Help indicates that the 1st parameter is the value to look for,
the 2nd is the range to look in (for that value), and the third is the column from which to take the answer when the row is selected. I don't want to look in column B for "1.1.6", I only want to look in column A, but I want to return the value that is in column B. Still did not get this working, but do have worksheetfunction.Match working, which is better because I actually want to capture 3 or 4 columns worth of data from that worksheet row, and this eliminates the need to lookup the value multiple times. Dale -- Email address is not valid. Please reply to newsgroup only. "Jim Thomlinson" wrote: You are looking for the value in the second column of a range that is only 1 column wide. Dim lookupRange as range set lookupRange = Worksheets("TaskInfo").Range("A2:B200") 'B200 not A me.text2.Value = application.worksheetfunction.vlookup("1.1.6", lookupRange, 2) -- HTH... Jim Thomlinson "Dale Fye" wrote: I'm an Access programmer, venturing into Excel VBA. I have a form that contains a text value ("1.1.6") that is one of many values in my TaskInfo Worksheet. I want to use Vlookup to get the values in the 2nd, 3rd, and 4th columns of this worksheet, but cannot seem to make it work. My code segment looks like: Dim lookupRange as range set lookupRange = Worksheets("TaskInfo").Range("A2:A200") me.text2.Value = application.worksheetfunction.vlookup("1.1.6", lookupRange, 2) But this generates a runtime error 1004: Unable to get the Vlookup property of the WorksheetFunction class When set the lookupRange in the Immediate window, I am able to debug.print lookupRange(1), etc, so I know the range is defined properly, but have know idea why I am getting this error message. Any help would be greatly appreciated. -- Email address is not valid. Please reply to newsgroup only. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using vLookup function in VBA
If match works for you then great.
FYI help for Vlookup says "Searches for a value in the leftmost column of a table". The second parameter is the table and it needs to include all of the columns from which you might want to return data. Note the examples in help all include multiple columns... -- HTH... Jim Thomlinson "Dale Fye" wrote: The VLookup Help indicates that the 1st parameter is the value to look for, the 2nd is the range to look in (for that value), and the third is the column from which to take the answer when the row is selected. I don't want to look in column B for "1.1.6", I only want to look in column A, but I want to return the value that is in column B. Still did not get this working, but do have worksheetfunction.Match working, which is better because I actually want to capture 3 or 4 columns worth of data from that worksheet row, and this eliminates the need to lookup the value multiple times. Dale -- Email address is not valid. Please reply to newsgroup only. "Jim Thomlinson" wrote: You are looking for the value in the second column of a range that is only 1 column wide. Dim lookupRange as range set lookupRange = Worksheets("TaskInfo").Range("A2:B200") 'B200 not A me.text2.Value = application.worksheetfunction.vlookup("1.1.6", lookupRange, 2) -- HTH... Jim Thomlinson "Dale Fye" wrote: I'm an Access programmer, venturing into Excel VBA. I have a form that contains a text value ("1.1.6") that is one of many values in my TaskInfo Worksheet. I want to use Vlookup to get the values in the 2nd, 3rd, and 4th columns of this worksheet, but cannot seem to make it work. My code segment looks like: Dim lookupRange as range set lookupRange = Worksheets("TaskInfo").Range("A2:A200") me.text2.Value = application.worksheetfunction.vlookup("1.1.6", lookupRange, 2) But this generates a runtime error 1004: Unable to get the Vlookup property of the WorksheetFunction class When set the lookupRange in the Immediate window, I am able to debug.print lookupRange(1), etc, so I know the range is defined properly, but have know idea why I am getting this error message. Any help would be greatly appreciated. -- Email address is not valid. Please reply to newsgroup only. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using vLookup function in VBA
If you want to return the 2nd, 3rd, and 4th column of the lookup range, then
that lookup range has to have at least 4 columns (and the key in the leftmost column). Dim res as variant 'could be an error dim myStr as string dim LookupRng as range mystr = "1.1.6" set lookuprng = worksheets("Taskinfo").range("a2:D200") '<-- A to D!!! 'not application.worksheetfunction.vlookup! res = application.vlookup(mystr, lookuprng, 2, false) if iserror(res) then me.text2.value = "No Match" me.text3.value = "no match" me.text4.value = "no Match" else me.text2.value = res 'use what you found me.text3.value = application.vlookup(mystr, lookuprng, 3, false) me.text4.value = application.vlookup(mystr, lookuprng, 4, false) end if I used false as that 4th argument. I would expect that since you're looking for a match with strings, that you'd want an exact match. ============ Using application.match() (not application.worksheetfunction.match()): Dim Res as variant dim myStr as string dim LookupRng as range mystr = "1.1.6" set lookuprng = worksheets("Taskinfo").range("a2:A200") '<-- Just Column A!! res = application.match(mystr, lookuprng, 0) if iserror(res) then me.text2.value = "No Match" me.text3.value = "no match" me.text4.value = "no Match" else Me.Text2.Value = LookupRng(Res).Offset(0, 1).value Me.Text3.Value = LookupRng(Res).Offset(0, 2).value Me.Text4.Value = LookupRng(Res).Offset(0, 3).value end if I could have used this syntax, too: Me.Text2.Value = LookupRng(Res, 2).Value Me.Text3.Value = LookupRng(Res, 3).Value Me.Text4.Value = LookupRng(Res, 4).Value ============= The reason I used application.match instead of application.worksheetfunction.match() is the way they handle errors (same reason for the vlookup() version, too). Application.match() returns a value that can be tested with if iserror(res) application.worksheetfunction.match() causes a run time error: On error resume next res = application.worksheetfunction.match(....) if err.number < 0 then 'no match err.clear me.text2.value = "no match".... else 'worked ok 'do that end if on error goto 0 I find the application.match() and application.vlookup() easier to use. Dale Fye wrote: I'm an Access programmer, venturing into Excel VBA. I have a form that contains a text value ("1.1.6") that is one of many values in my TaskInfo Worksheet. I want to use Vlookup to get the values in the 2nd, 3rd, and 4th columns of this worksheet, but cannot seem to make it work. My code segment looks like: Dim lookupRange as range set lookupRange = Worksheets("TaskInfo").Range("A2:A200") me.text2.Value = application.worksheetfunction.vlookup("1.1.6", lookupRange, 2) But this generates a runtime error 1004: Unable to get the Vlookup property of the WorksheetFunction class When set the lookupRange in the Immediate window, I am able to debug.print lookupRange(1), etc, so I know the range is defined properly, but have know idea why I am getting this error message. Any help would be greatly appreciated. -- Email address is not valid. Please reply to newsgroup only. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to combine Combo Box function with Vlookup function | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION | Excel Worksheet Functions | |||
how do I write a vlookup function within an iserror function so t. | Excel Worksheet Functions | |||
I want to use Vlookup function and AND function in a single formu. | Excel Worksheet Functions |