CODEWITHSUNDEEP
excelvbaformattingvlookup
Trimax
Trimax

Reputation: 2473

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

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.

Upvotes: 5

Views: 47929

Answers (6)

Sharif
Sharif

Reputation: 1

In Microsoft Office 2016.

Once data copied to the cell. Select the column which needs to be in date format. Right click->format cells, go to Number tab and select date from the drop-down and choose the required date format, data will be converted to date format.

Upvotes: 0

Prasand Kumar
Prasand Kumar

Reputation: 165

Excel Formula :

=TEXT(VLOOKUP(Lookup_value,table_array,col_index_num,0),"dd-mm-yyyy")

VBA :

Sub vlookup_code()

    Dim table_rng As Range

    'FOR THE TIME BEING GIVING A LOOKUP VALUE
    look_up_value = "sekar"

    'SETTING THE TABLE ARRAY RANGE
    Set table_rng = Sheet1.Range("C3").CurrentRegion

    'SINCE VLOOKUP WILL GIVE AN ERROR, IF THE LOOKUP VALUE IS NOT PRESENT
    'TO HANDLE THIS ERROR, WE USE THE ISERROR STATEMENT
    If IsError(Application.vlookup(look_up_value, table_rng, 2, 0)) = False Then

        vlook_value = WorksheetFunction.vlookup(look_up_value, table_rng, 2, False)

        'CHANGING THE VLOOKUP VALUE FORMAT TO DATE FORMAT
        date_format = WorksheetFunction.Text(vlook_value, "dd-mm-yyyy")

    End If

End Sub

Upvotes: 1

Shalini Baranwal
Shalini Baranwal

Reputation: 2998

i faced the same issue. After vlookup, change the format to "ShortDate" format. This will give you what you are looking for.

Upvotes: 0

Kenneth Westervelt
Kenneth Westervelt

Reputation: 21

I had the same problem, and simply reformatted the VLOOKUP cell to also be in date-time format. That fixed my issue. I am not sure how date-time gets translated into a number, but it does.

Upvotes: 0

Vasily
Vasily

Reputation: 5782

use this 

Sheets(3).Cells(num_row, last_col_s1 + num_col - 1).Value = cdate(my_value)

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71207

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.

Upvotes: 2

Related Questions