Problem using Vlookup as a function in VBA.

  • Thread starter Thread starter Tony A. Steane
  • Start date Start date
T

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
 
Tony,

Try changing

Application.WorksheetFunction.VLookup

to just

Application.VLookup

That is often the problem.

HTH,
Bernie
MS Excel MVP
 
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
 
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
 
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.
 
glad to help

--
Don Guillett
SalesAid Software
(e-mail address removed)
Joelle Turner said:
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
Click to expand...
 
Back
Top