Vlookup Cannot Find a Particular Date in a Range?

[CCL]

When I use a Vlookup function in VBA to search the record entry dates from a
range, it cannot find the record and just returns the run-time error. I am
sure that the particular dates exist in the range since I have used Countif
in VBA and Vlookup in worksheet to test if it exists. Could anybody tell me
why?

Price=Application.WorksheetFunction.Vlookup(EntyDate,Sheets("Price").Columns("A:B"),2,0)

Thanks

 

[Niek Otten]

Use Range instead of Columns

Price=Application.WorksheetFunction.Vlookup(EntyDate,Sheets("Price").Range("A:B"),2,0)

But if a date is not there, it will look through the entire column. So it is better to limit the range to the rows that actually
contain data.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| When I use a Vlookup function in VBA to search the record entry dates from a
| range, it cannot find the record and just returns the run-time error. I am
| sure that the particular dates exist in the range since I have used Countif
| in VBA and Vlookup in worksheet to test if it exists. Could anybody tell me
| why?
|
| Price=Application.WorksheetFunction.Vlookup(EntyDate,Sheets("Price").Columns("A:B"),2,0)
|
| Thanks
|

 

[Niek Otten]

Dim Entrydate as Double worked for me

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi Niek,
|
| Thank you for you advice. I have tried it in Excel 2000 and Excel xp, but
| failed. I still got the run-time error 1004. The following is my sample
| program for your information. Do you have any idea?
|
| Sub TestExtreivePrice()
|
| Dim EntryDate As Date
| Dim Price As Double
|
| EntryDate = Sheets("Price").Range("G1").Value ' G1=02 Dec 2006
|
| Price = Application.WorksheetFunction.VLookup(EntryDate,
| Sheets("Price").Range("A2:B12"), 2, 0)
|
| MsgBox "Price on " & EntryDate & " = " & Price
|
| End Sub
|
| Thanks a lot
|
| "CCL" ??:
|
| > When I use a Vlookup function in VBA to search the record entry dates from a
| > range, it cannot find the record and just returns the run-time error. I am
| > sure that the particular dates exist in the range since I have used Countif
| > in VBA and Vlookup in worksheet to test if it exists. Could anybody tell me
| > why?
| >
| > Price=Application.WorksheetFunction.Vlookup(EntyDate,Sheets("Price").Columns("A:B"),2,0)
| >
| > Thanks
| >