VLOOKUP Issue

In debugging a problem I found in a workbook I've created, I isolated the issue to the results that VLOOKUP are returning.

I have a simple table against which I lookup a value and then do a calculation based on the values in adjacent columns.

I've created the results I desire perfectly within cells on the spreadsheet. However, when I transfer the same formulas to VBA to have the work performed as a macro, I do not get the same results around the edges of my data.

The table is incremented from zero to 1,000,000 in various steps. In the VBA version, any lookup value less than 100,000 or greater than 950,000 does not return the same results as the formulas in the spreadsheet cells.

It's my undertadning that when the 4th element of VLOOKUP is omitted, that the next closest value that is less than the lookup value is returned. Within VBA, this doesn't happen when the lookup value is between the first and second value, or the second to last and last value in my list. But it does when used as formula in a cell.

I'm stumped! Please see the attached file for an example. It demonstartes the cells doing the formulas, and below that the result VBA returns doing the same work.

Any advice?

Thanks,

Shred

you must have XL 2007, since I cannot open your file with 2003. I have to assume that you are using the VBA WorksheetFunction property as in WorksheetFunction.Vlookup().

I believe that property is known to be unreliable in 2003. I suspect 2007 inherited the flaw as well.

I suggest you utilize another method in VBA to locate your target value - maybe .Find, although you will have to tweak it for the closest value case when an exact match is not found.

If you are unfamiliar, simply search VBA help for Find Method. It will give you a good idea.

HTH

Conor

I tested your attachment in Excel 2003, and I get the same result for both the formula and the VBA results.

I noticed one thing that might give you some unexpected trouble: You have used value as a variable name. Value is already a keyword used in VBA. You should avoid using variable names that is the same as keywords used in VBA code.

I don't think this is the source of your trouble, as it worked fine for me, but it could be that 2007 version behaves different.

Bjornar:

Thanks for your reply. I am using 2007. So it owrked in 203 with a value of 951000 or 99,000? Perhaps it is an issue with 2007?

I appreciate your variable naming comments. I just threw that together to exemplify the issue. The real workbook I use this in has different names in there. I'm using similar code to populate a userform, that's when I started noticing incorrect results.

Thanks for taking the time to reply.

I am using 2007. Attached in the zip archive is a version of the file savd in the 2003 format. Another poster has replied that it worked in 2003. I do not have a copy of 2003 on the machine I'm at to test it myself. Perhaps you could validate?

I am using vlookup in conjunction with worksheet.function to get the results. I'll have a look a the Find technique . thanks for the tip.

Shred.

I opened your 2003 file, and see that you got different results for the formula and VBA results with 951000. I tried to enter 952000 and then VBA result changed so I got the same result from both formula and VBA. Tried to enter 951000, still same result. Then 99000, still identical result on both formula and VBA.

It could be a bug in 2007 ?