How can I keep the date format from a VLookup returned value?

Question

I have a sheet whit mixed format data (dates, double, strings, etc.). I search the value (my_index) in the lookup_range and I want to retrieve the data to change with it a cell in other sheet. It works fine, but when the value returned by VLookup is a date and I set it to the other sheet it looses its date format.

Dim lookup_range As Range
Dim my_index, my_value As Variant

my_value = Application.VLookup(my_index, lookup_range, num_col, False)
Sheets(3).Cells(num_row, last_col_s1 + num_col - 1).Value = my_value

So, when the data in the lookup_range is 02/05/2014 the data showed at sheet-3 looks like 41761.

I need to keep the original data format of the data in the lookup_range.

Solution

VLOOKUP doesn't care about the formatting of the data it's returning - it returns data, not formats.

The only way to ensure the same format as the source, is to copy/paste that format, either manually or programmatically.