Problem using Vlookup as a function in VBA.

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
0
tsteane (3)
3/5/2004 3:08:56 AM
excel 39879 articles. 2 followers. Follow

5 Replies
739 Views

Similar Articles

[PageSpeed] 54

Tony,

Try changing

Application.WorksheetFunction.VLookup

to just

Application.VLookup

That is often the problem.

HTH,
Bernie
MS Excel MVP

"Tony A. Steane" <tsteane@hotmail.com> wrote in message
news:jqpf405art89stqaomvj667ttfbffvmava@4ax.com...
> 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


0
Bernie
3/5/2004 1:22:21 PM
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

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Tony A. Steane" <tsteane@hotmail.com> wrote in message
news:jqpf405art89stqaomvj667ttfbffvmava@4ax.com...
> 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


0
Don
3/5/2004 1:36:05 PM
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

"Don Guillett" <donaldb@281.com> wrote in message
news:uay0NbrAEHA.2292@TK2MSFTNGP12.phx.gbl...
> 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
>
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com


0
3/5/2004 11:12:51 PM
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.


In article <40490976$1_1@news.iprimus.com.au>,
 "Joelle Turner" <joelle_turner@hotmail.com> wrote:

> 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.?
0
jemcgimpsey (6723)
3/6/2004 12:52:39 AM
glad to help

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Joelle Turner" <joelle_turner@hotmail.com> wrote in message
news:40490976$1_1@news.iprimus.com.au...
> 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
>
> "Don Guillett" <donaldb@281.com> wrote in message
> news:uay0NbrAEHA.2292@TK2MSFTNGP12.phx.gbl...
> > 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
> >
> > --
> > Don Guillett
> > SalesAid Software
> > donaldb@281.com
>
>


0
Don
3/6/2004 1:27:51 AM
Reply:

Similar Artilces:

Crazy Sorting Problem.....
Ok so I have colums A though D that need to line up with E through P. I have a common symbol in D and F. I need to get rid of everything in colum A through D that does not line up with the E through P useing the common symbol as a referance. I am right now deleting extra info by hand. Please help. Hi see your other post -- Regards Frank Kabel Frankfurt, Germany Neal wrote: > Ok so I have colums A though D that need to line up with E through P. > I have a common symbol in D and F. I need to get rid of everything in > colum A through D that does not line up with the E through P ...

WaitForSingleObject Problem
Hi all, I use WaitForSingleObject(hProcess, INFINITE) to wait for execution of the MS ActiveX Control Pad Setup (started out of my application). <code> CreateProcess(NULL, "setuppad.exe", NULL, NULL, FALSE, DETACHED_PROCESS, NULL, NULL, &startupInfo, &processInfo) WaitForSingleObject(processInfo.hProcess, INFINITE) DoSomethingElse </code> At the beginning of installation everything is fine, but after a little bit of time, the CPU usage is 50% and nothing is going on (it's after searching for installed components). When I don't use WaitForSingleOb...

Problem Syncronizing.
HI, I have problems with a syncronized store that I had to syncronize again from the beginning. Give me alot of TIme out when I use the 401 Worksheet. Since some of the data is in the HQ Database I would like to know if there any operation I can do so then tell to the HQ client that I just want to upload some information?. Thanks in advance for your help. Rgds Rodrigo Go to Manager, Help, About. If your version is 1.2.0151 you have a version that won't work with Global Customer turned on. Contact MBS and ask for a Hot Fix to address this issue. Install the Hot Fix on all PC...

INSERT INTO problems
Hi all, I hope this doesn't appear twice, I did it a while ago and it never came up! So here goes again! I am trying to use INSERT INTO to insert one record in to a table (tna feedback) in two fields (USER ID: and DESK/LAPTOP). The values are generated by two different forms; stUser comes from this form and stMachine comes from another form which has been hidden once the user has chosen the value. This part works fine. However, every time I run it I get an "Incomplete Query clause" error message but can't fidure out what I've done wrong! Here is the code: Dim SQL ...

Access 2000 Package and Deployment Wizard problem!
I have created an Access 2000 application that I now wish to distribute to users as an MDE file. The Access 2000 Package and Deployment Wizard does a fine job of storing the relevant installation files on a CD, exploiting A2K Runtime. However, I would prefer to let users download my application from a website. The Package and Deployment Wizard offers a 'Web Publishing' deployment option that appears to address this need. My only problem is that it doesn't work for me. I get the following error message, Unexpected error number 48 has occurred: File not found: WebPost.dll I...

last 4 wk average using calculated pivotitem
Hi, I am trying to create a pivot table/chart from data that is listed b week. So my table has "week" as the row field and "total X" as th column field. I want the chart to display the total for each week as bar (the easy part) and also to graph the average of the last fou weeks as a line on top of the bar graph. I have tried to figure ou how to add a calculated pivot item but I can't seem to get it to wor right. So for each week there would be a second almost subtotal lik entry that calculates the average for the previous four weeks (if ther aren't four previo...

Process all cells in a (user) selection
I am using this code to give me cell by cell access to a code defined selection; Dim aCell As Range For Each aCell In Sheet4.Range("A4:A34") ' Do Stuff Here Next aCell which processes every cell in the A4:A34 Range How do I do the same thing for a selection drawn by the user before pressing my 'Process' button ? Thanks On 05 May 2010 11:10:21 GMT, Isis <isissoft@NOSPAMbtinternet.com> wrote: >I am using this code to give me cell by cell access to a code defined >selection; > >Dim aCell As Range >For Each aCell In S...

SQL Back-end / Access Front-end using linked tables????
I have a backend that contains tables that I pull Driver information, Customer information , etc. (these are MAS 200 accounting software tables that are exported nightly to the SQL backend). I also have on the backend tables that I push information to; information that we enter on the forms located on the front end. I might not have this set- up correctly, but I'm linking all tables on the back end to the Access database on the front end. When trying to set up an Auto Lookup off of a query I've created in Access it says I need to go into the table change Data Type to the Lookup wizard,...

Count problems[Excel 97]
Hi there, I did a search on the forum to find an answer to my question but didn' find anything. Here is my prob : Lets say I got this page-> ___a___b_____c__d__ 1 Name Type Job bla 2 Name Type Job bla 3 Name Type Job bla 4 Name Type Job bla 5 Name Type Job bob I want a Cell to write how many row I got( 5 in this exemple) and onl count the cells with bla in the D column(4 in this exemple). Sorry if I'm not really clear but if you can help me feel free t answer :) thx, Tulkas -- Tulka -----------------------------------------------------------...

Problems with german characters (umlaute)
Hello colleagues, it happes every months that a mail reaches us with I don't know how coded german characters. Our german customers are calling us, asking what we're missing on our system. I've checked one of these mails and the only thing I found out that the codepage, which was specified in the header, was the same than in the other mails I've in my mailbox and which I can read perfectly. The strange thing in current case is, that the email is displayed correctly when the mail is opened in Outlook Web Access. I need some help how to get hand on these problems. Many...

How to view the code for excel built-in functions?
Is it possible? -For example the function PMT(). thanks. No, the code is compiled, so it would likely be less than useful anyway. About the best you can do is check out the equations used in Help (see "PV"). In article <OSU3OXOBGHA.1676@TK2MSFTNGP09.phx.gbl>, "serdar" <s@s.com> wrote: > Is it possible? -For example the function PMT(). > thanks. ...

Using XP Briefcase with money file
I put a copy of my money file into the laptop briefcase and I am able to keep them in sych for a while. For some reason I get locked out, the computer knows the file has been updated but it will not let the desk top file update the laptop file, I get the following message; "The source file can not be opened" "Check to see if the other program has the file opened, and close the file before updating the briefcase." Other files update just fine. When I get this message, I can't even drag a new copy of the Money file to my briefcase, I have to rename the file an...

Using a VBscript to copy/delete/move Outlook messages
I need to write a VBscript to: - open my Outlook (2000 or XP) mailbox - read the list of the folders in either the Exchange Mailbox and in local ..pst files - open each folder and subfolders - read information about messages (i.e. sender, recipient, creation date, and so on) - move, copy or delete messages based on the above information Can anybody please provide a pointer to a sample I can start from? Regards Marius ...

VBA- Application.Wait?
Does PowerPoint not have "Application.Wait" like Excel? Is there another way to pause code for a few seconds in PPT 2003/2007 while a small bat file runs? -Mel On 3/10/10 2:29 AM, Mel wrote: > Does PowerPoint not have "Application.Wait" like Excel? Is there > another way to pause code for a few seconds in PPT 2003/2007 while a > small bat file runs? > > -Mel From Example 8.4 on my Web site (http://www.PowerfulPowerPoint.com/): Sub Wait() waitTime = 5 Start = Timer While Timer < Start + waitTime DoEvents ...

Exchange Features Tab in ADUC when using Windows 2000 SP3
Hi, I would like to run Active Directory Users and Computers with Exchange 2003 Admin tools I'm limited to Windows 2000 Prof/SP3 Everything works,except from the Exchange Features tab. When selecting this tab, ADUC hangs. Any ideas. (I have to stay on SP3!) br TN Terminal Services is always my first choice. On Wed, 13 Apr 2005 23:35:22 +0200, "newsMS" <t_n@trashcan.dk> wrote: >Hi, > >I would like to run Active Directory Users and Computers with Exchange 2003 >Admin tools >I'm limited to Windows 2000 Prof/SP3 > >Everything works,except...

countif problem.
I wrote a formula in a cell that contains the following: =countif(f5:f268,"> char(32)") / countif(e5:e268,"> char(32)") I have verified that any unused cells have blanks spaces in them. Both columns have data in the same rows so the counted value for each column should be the same but it isnt. All cells contain only numbers or letters and the cells format is general. Any suggestions?? Not sure I get it, but try =countif(f5:f268,">"&char(32)) / countif(e5:e268,">"&char(32)) "psion" <oregonsparkie@hotmail.com> w...

Problem with printing listbox bigger than one page
Hi, I created form "Invoice". It contains few list boxes (like: Brand, Product, Price, etc.). Each list box is big enough to display 50 items. It works in this way: user is chosing product from table Products_tbl and clicks on "Add to invoice" button which adds to each list box new item containg apropriate information about chosen product. At the end, user can print the form. Before it was limited, and user could put max. 50 products on one invoice, but I wanted to get rid of this limitation. I did it in this way: after 50th item, with each added product I extend list boxe...

Carriage Return/Line Feed Problem
Hi guys, I wrote some code that gets the HTML from a Web page and stores it in a buffer, then I display the buffer in a big multiline CEdit box. The problem is that on many pages I don't get new lines, I just get a thick solid verticle line kinda like this: || where it should return. Also, if I view the pages (view source) in Notepad they look fine. Most pages do that, but interestingly Microsoft pages do the proper new line and look great in my edit box. Maybe I have to replace all the \r with \r\n or something like that ? Does a \r\n still only take up 1 char ? I download the page in...

FRx
Dear, I encountered problem of changing default base period. The version of FRx software is 6.5.28 and it is running under Windows 2K Professional. The situation was: After opened the Catalog of Report Window, there was a pop-up message box which said 'Changed the default base period to C'. I did not know why this could happen. - Would you please tell me under what circumstances this pop-up message box would display?? In order to continue to use this software, I only could click 'OK' from the message box. The report date is then changed to 'Default Base Period' - (Ja...

Audience rule, problem with Domain Local Group.
I try to create a rule for my audience on Portal 2007 User = Member of --- some Domain Local Group (I need to add people from other trusted forest) But it can't be relolved :-( What's wrong? I checked it on 3 Portals, no luck I cam only create such a rule: Work e-mail Contains @needed_domain.com or add individual accounts from those domains, but there are 1000, too hard to add manually .... Please help. Perhaps there is another way These groups need to be either Global or Universal groups. Universal groups are better for SharePoint, and they can contain global...

Using a variable for a chart limit
Since I got instant help on my last query, is there any way to use a cell reference as an axis minimum or maximum in format axis? It seems impossible, but there is a lot of experience out there. Thanks in advance. -- Vince F Hi, See Jon's information http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html Cheers Andy Vince F wrote: > Since I got instant help on my last query, is there any way to use a cell > reference as an axis minimum or maximum in format axis? It seems impossible, > but there is a lot of experience out there. Thanks in advance. -- Andy Pop...

backup problem
Hi I had installed backup for office 2003, I had been backing up to a removable disk. I had removed the disk and kept getting messages that was unable to back up to removable disk, even though I have turned off all automatic backup options. I have since removed back up "feature" but still get the 4 or 5 steps stating unable to find disk etc, any ideas how to get rid of this wonderful microsoft feature. thanks tim If by removed you mean 'uninstalled' then it would appear the uninstall wasnt successfull. In OL ensure backup is not selected within Addins "Tim&...

shortcut/hotkey-problem
Hello NG, I have set a custom hotkey to a shortcut/program on my Windows XP Start Menu (CTRL-ALT-B). However, this programlink was deleted. If I now try to reassign this shortcut, it does not work - it seems that the combination has been still reserved for a non-existing link. Is it possible to unmap the combination to map it then again? Hopefully someone can help me. Thank you, Johannes Perhaps the info in this thread will help. http://www.tomshardware.com/forum/32795-45-find-delete-hotkey-shortcut-assigned-program johannes pfeffer wrote: > Hello NG, > ...

Outlook 2003 Personal Address Book forwarding problem
This Outlook 2003 is connected to a Exchange 5.5 Server The problem is this, everytime I try to forward any e-mail and use any distribution list on the Personal Address Book it does not want to send the e-mail but if I try to send it any other way it works fine. Thanks ...

LDAP problem
I upgraded from Outlook 2000 to 2002 and now every time I open Outlook I get a Microsoft LDAP Directory window with Server Name, Port, User Name, and Password. What is this thing and how do I get rid of it? -- Mike D. www.stopassaultnow.net Remove .spamnot to respond by email --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.789 / Virus Database: 534 - Release Date: 11/7/2004 You can remove the LDAP address book provider via Tools | Email Accounts | View/Change address books. (Might be listed as Active Directory or LD...