sharadk74
04-14-2008, 05:51 AM
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
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