VBA Private Sub - How to use DATEVALUE in a VLOOKUP

jnathan

New Member
Joined
Jul 8, 2013
Messages
48
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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!
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,695
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top