Solved: issue with using DATEValue in VBA, with VLookup

[sharadk74]

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

[mikerickson]

It looks like exp1 is (6/16/2007). Change it so that it returns ("6/16/2007")

[sharadk74]

Hi Mike,
Thanks for your reply. Your answer was a good hint. The string I was trying to create is dynamic , as per the formula in the original mail .The date was a variable, so the Quote marks could not be added to the string.
I got 2 workarounds to the problem.
I used NumberFormat= "General" for the XL sheet column being used for VLOOKUP in the VBA Code. This converted the column to the XL number representation in the Sheet itself, and I did not need to use DATEVALUE in VBA.
For the issue of adding quote marks to a dynamic string in VBA, use

 mychr=  Chr(34)
MyStr= dynamic part+ mychr+ " Fixed part"