Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup Cannot Find a Particular Date in a Range?
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(EntyDa te,Sheets("Price").Columns("A:B"),2,0) Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup Cannot Find a Particular Date in a Range?
Use Range instead of Columns
Price=Application.WorksheetFunction.Vlookup(EntyDa te,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 "CCL" wrote in message ... | 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(EntyDa te,Sheets("Price").Columns("A:B"),2,0) | | Thanks | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup Cannot Find a Particular Date in a Range?
Dim Entrydate as Double worked for me
-- Kind regards, Niek Otten Microsoft MVP - Excel "CCL" wrote in message ... | 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(EntyDa te,Sheets("Price").Columns("A:B"),2,0) | | Thanks | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup(date, range,3) | Excel Worksheet Functions | |||
using vlookup to find data outside a specified range | Excel Worksheet Functions | |||
Find Last cell in Range when range is date format | Excel Discussion (Misc queries) | |||
How to find date in a range? | Excel Programming | |||
Find date and copy range based on that date | Excel Programming |