I want to look up a date in column A in a file, using Vlookup, or some other function, and copy the data in col 2 of that row to another worksheet
2. The date string is dynamic, and I'm using the Datevalue function as input for VLookup, but I cannot get the correct syntax when i populate the formula in the cell using VBA.
If I enter the formula manually, the Vlookup is working correctly.
The VBA Code I use is:Selection.Formula = "=vlookup(DATEVALUE" & exp1 & ", 'D:/TA/[" & bookname & "]Sheet1'!A1:C1999,2,FALSE))"

exp1 is the derived date in the format mm/dd/yyyy. I have tried to set exp1 in different formats, but the value is not being converted to text.

On entering the formula manually (and recording in macro Recorder), the syntax is:-
vlookup(datevalue("6/16/2007"), 'D:/TA/[ABC.xls]Sheet1'!A1:C1999,2,FALSE))
This works correctly.
THe output of the VBA string given earlier is
=vlookup(datevalue(6/16/2007), 'D:/TA/[ABC.xls]Sheet1'!A1:C1999,2,FALSE)) The quotes around the date do not appear when the formula is inserted dynamically using VBA.
Excel DATEVALUE function is different from Datevalue function in VBA.
Another related issue is that On Manual Entry, when i run the macro, I get UpdateLinks:filename file selection msg box, even though I have added these two lines to the beginning of the macro.

Application.AskToUpdateLinks = False
Workbooks.Application. ActiveWorkbook.UpdateLinks = xlUpdateLinksNever


Thanks in advance for all help.

Sharad

Sharadk74,

Try:
Range("A20").Formula = "=VLOOKUP(DATEVALUE(""6/16/2007""),'D:\TA\[ABC.xls]Sheet1'!A1:C1999,2,FALSE)"


Have a great day,
Stan

stanleydgromjr

Windows Vista Business and Excel 2003, 2007.

Hi, I managed to find the solution of this problem by using numberformat= "General" for the column of dates which I was looking up through VLookup. This prevented the need for using DATEVALUE function in VBA.
A related solution for inserting quote signs in a dynamic string is
Rgds.,
Sharad