another Vlookup question

[c1802362]

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

 

[Per Jessen]

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

 

[Dave Peterson]

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]

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