Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
another Vlookup question
I've searched through the archives about using
worksheetfunction.Vlookup in VBA, but I'm getting an error I haven't seen before. I'm running Excel 2003. My routine takes a large data base, reduces the data into an A x B table, then uses the data in the table against a B x C table. For diagnostics and ease of following the math, both arrays are written to a temporary worksheet and given range names. So, my code loops though one of the named ranges, does the lookup, then sums the result into a final array: deptArray(i,j)=deptArray(i,j) + WorksheetFunction.VLookup (ActiveCell.Offset(j,0), "RangeName1", k, False) where i,j are the array bounds, RangeName1 is the named range of one of the arrays, k is the column number of the value to return, and activecell is the upper leftmost corner of the second named range. The error I get is : Runtime error '1004' Unable to get the VLookup property of the WorksheetFunction Class I've got all the standard add-ins on the Excel side running, and no available add-ins on the VBA side. Any suggestions? Art |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
another Vlookup question
Hi
Something is wrong with your formula. Assuming RangeName1 is a named range, this should do it: deptArray(i,j)=deptArray(i,j) + WorksheetFunction.VLookup (ActiveCell.Offset(j,0), Range("RangeName1"), k, False) Regards, Per On 24 Okt., 02:11, c1802362 wrote: I've searched through the archives about using worksheetfunction.Vlookup in VBA, but I'm getting an error I haven't seen before. I'm running Excel 2003. My routine takes a large data base, reduces the data into an A x B table, then uses the data in the table against a B x C table. For diagnostics and ease of following the math, both arrays are written to a temporary worksheet and given range names. So, my code loops though one of the named ranges, does the lookup, then sums the result into a final array: deptArray(i,j)=deptArray(i,j) + WorksheetFunction.VLookup (ActiveCell.Offset(j,0), "RangeName1", k, False) where i,j are the array bounds, RangeName1 is the named range of one of the arrays, k is the column number of the value to return, and activecell is the upper leftmost corner of the second named range. The error I get is : * * * * * * * * Runtime error '1004' * * * * * * * * Unable to get the VLookup property of the WorksheetFunction Class I've got all the standard add-ins on the Excel side running, and no available add-ins on the VBA side. Any suggestions? Art |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
another Vlookup question
I think you have a couple of problems with your code.
If there isn't a match, then you'll see a run time error with worksheetfunction.vlookup(). I use application.vlookup() so that I can check to see if an error was returned. And "rangeName1" is a string--it's not a range. I'd use something like: Dim Res as variant 'could be an error ..... res = application.vlookup(activecell.offset(j,0).value, _ worksheets("Somesheetnamehere").range("Rangename1" ), _ k, false) 'now you can check for an #n/a error using: if iserror(res) then ... Then you could check for a number -- since you want to do an add. Or you could just check for a number: if isnumeric(res) = false then res = 0 'add 0 to the sum??? end if deptarray(i,j) = deptarray(i,j) + res c1802362 wrote: I've searched through the archives about using worksheetfunction.Vlookup in VBA, but I'm getting an error I haven't seen before. I'm running Excel 2003. My routine takes a large data base, reduces the data into an A x B table, then uses the data in the table against a B x C table. For diagnostics and ease of following the math, both arrays are written to a temporary worksheet and given range names. So, my code loops though one of the named ranges, does the lookup, then sums the result into a final array: deptArray(i,j)=deptArray(i,j) + WorksheetFunction.VLookup (ActiveCell.Offset(j,0), "RangeName1", k, False) where i,j are the array bounds, RangeName1 is the named range of one of the arrays, k is the column number of the value to return, and activecell is the upper leftmost corner of the second named range. The error I get is : Runtime error '1004' Unable to get the VLookup property of the WorksheetFunction Class I've got all the standard add-ins on the Excel side running, and no available add-ins on the VBA side. Any suggestions? Art -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
another Vlookup question
Per/Dave -
Thanks - declaring the named range as a Range solved the problem. Without any VBA documentation on VLookup, I used the excel documentation as a guide. In excel, the range name in parentheses declares the range. In VBA, the range has to be declared. Anyway, much obliged, everything works wonderfully. Art |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp Question | Excel Worksheet Functions | |||
Another Vlookup for VBA question | Excel Programming | |||
Vlookup Question | Excel Programming | |||
IF/AND VLOOKUP question... | Excel Discussion (Misc queries) | |||
Vlookup question | Excel Discussion (Misc queries) |