Home / Questions / 475
Accepted
Trimax
Asked: 2020-12-06 03:43:27 +0800 CST 2020-12-06 03:43:27 +0800 CST

How can I preserve the format of a data obtained with the VLookup() function?

  • 772

I have a sheet with data in different formats (dates, decimals, text, etc.).

I am looking for a value ( my_index) in a range ( lookup_range) and I want to retrieve the data to write it to a cell on another sheet.

The macro works fine, but when the value I retrieve from the function VLookupis a date and I copy it to the other sheet, it loses its formatting.

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

Therefore, when the data in lookup_rangeis 02/05/2015, the data copied to Hoja 3is displayed as if it were the number 41761.

I need to preserve the original format that the value has in the Hoja 1when being copied to the Hoja 3.

excel

1 Answers

  1. Best Answer

    "Native" functions like vlookup()Excel's return values ​​or information about a cell but not both.

    The alternative is to use copy and paste or special copy and paste in one of its variants. Here is an example of copy and paste.

    Sub ConservarFormato()
    Dim rgOrigen As Range, rgDestino As Range
    ActiveSheet.Activate
    Set rgOrigen = Range("B1")
    Set rgDestino = Range("F2")
    rgOrigen.Copy Destination:=rgDestino
End Sub
    • 3