Problem using Vlookup as a function in VBA.

[Tony A. Steane]

Using xl2000:
If anybdy could assist me I would be thankful.

I'm trying to use the VLOOKUP function within a Public declared
function as such :

Public Function color(address As Range)

ColorRange = Worksheets("sheet1").Range("range")

color = Application.WorksheetFunction.VLookup(address.Value,
ColorRange, 2)

End Function

and the worksheet is set out as such

A B C D

1 04-Mar-04 orange lime
2
3
4 01-Mar-04 grey
5 02-Mar-04 black
6 03-Mar-04 red
7 04-Mar-04 orange
8 05-Mar-04 white
9 06-Mar-04 green
10 07-Mar-04 brown
11 08-Mar-04 purple
12 09-Mar-04 lime


where range is Defined as A4:B12

cell A1 is the date entered for lookup_value

cell C1 uses the =VLOOKUP(A1,Range,2) formula
and in this example returns the correct color for the
date supplied.

Cell D1 formula is =Color(A1) which uses the function included
above, however the function return the value "lime" and not
"orange".

could somebody explain what I'm doing wrong.....

Thanks

Tony

 

[Bernie Deitrick]

Tony,

Try changing

Application.WorksheetFunction.VLookup

to just

Application.VLookup

That is often the problem.

HTH,
Bernie
MS Excel MVP

 

[Don Guillett]

try this. Main problem was SET

Public Function color(address As Range)
Set colorrange = Worksheets("sheet1").Range("g2:h6")
color = Application.VLookup(address, colorrange, 2)
End Function

 

[Joelle Turner]

Many thanks Don and Bernie

In this instance, it was the use of the SET that corrected the problem,
not really sure why, but I should be able to read up on that!

It was interesting to note that the Application.WorksheetFunction.Vlookup
and the Application.Vlookup both worked.

Is there a reason why the WorksheetFunction may be omitted.?

Cheers

Tony

 

[JE McGimpsey]

WorksheetFunction is a collection that was added (IIRC, in XL97). Before
that, all functions were child methods of the Application object. For
backward compatibility, the Application.<function> syntax was kept. With
some versions of XL (97/MacXL), VLookup wasn't implemented properly as
part of the WorksheetFunction collection and the only way to use it is
as a method of the Application.

 

[Don Guillett]

glad to help

--
Don Guillett
SalesAid Software
(e-mail address removed)

Many thanks Don and Bernie

In this instance, it was the use of the SET that corrected the problem,
not really sure why, but I should be able to read up on that!

It was interesting to note that the Application.WorksheetFunction.Vlookup
and the Application.Vlookup both worked.

Is there a reason why the WorksheetFunction may be omitted.?

Cheers

Tony