VBA Private Sub - How to use DATEVALUE in a VLOOKUP

[jnathan]

I am trying to get a DATEVALUE from another worksheet by using Vlookup.

The reason for this is the worksheet I am trying to Vlookup from is not storing the dates as dates and I am unable to simply change the data into a date format as it is 'controlled' by someone else.

Currently, the following Vlookup has successfully pulled in all the required data.

If Target.Column = 2 Then
If IsNumeric(Target.Value) Then
If IsNumeric(Target.Value) Then
Target.Offset(0, 20) = Evaluate("=VLOOKUP(" & Target.Value & ",'[GRL Alerts.xlsx]Sheet1'!$A:$W,2,FALSE)")

I just need a bit of help in adapting the 'Evaluate' part of the code so that it pulls in the data in date format by using DATEVALUE.

Any help would be much appreciated

Justin

 

[VBA Geek]

How about using VBA function "DateSerial" to convert it to date? DateSerial(YYYY, MM, DD)

3 arguments: 4 digits for the year, 2 digits for the month and 2 digits for the day. I find this one very helpful at times!

 

[jnathan]

Thanks - how would I use DateSerial within the code below?

Target.Offset(0, 20) = Evaluate("=VLOOKUP(" & Target.Value & ",'[GRL Alerts.xlsx]Sheet1'!$A:$W,2,FALSE)")

How about using VBA function "DateSerial" to convert it to date? DateSerial(YYYY, MM, DD)

3 arguments: 4 digits for the year, 2 digits for the month and 2 digits for the day. I find this one very helpful at times!

 

[VBA Geek]

what does the Vlookup return? a Date but showing as a number, for instance 12/12/2013 would be 41620 or a text string that looks like a date but not recognized as such by Excel?

 

[jnathan]

not exactly. it's showing a date however it is in dd-mm-yyy format stored as General. I need the date to come through in date format shown as dd/mm/yyyy.

But basically, you're right in saying that it's not recognised as a date - I need to doudble-click the cell for it to then be changed into a date format

what does the Vlookup return? a Date but showing as a number, for instance 12/12/2013 would be 41620 or a text string that looks like a date but not recognized as such by Excel?

 

[VBA Geek]

You can try two ways. first is to wrap vba function "CDATE" around the vlookup, without the evaluate.

if that does not work then you could do

dim TEMP
Temp = VLOOKUP(" & Target.Value & ",'[GRL Alerts.xlsx]Sheet1'!$A:$W,2,FALSE)
Target.Offset(0, 20) = FORMAT(dateserial(right(temp,4), mid(temp,4,2), left(temp,2), "dd-mm-yyyy")

basically store the vlookup result in TEMP and then convert it to a date using dateserial and print it back to the worksheet

 

[jnathan]

Many thanks! It worked!!!