CODEWITHSUNDEEP
excelvbadate
Pericles Faliagas
Pericles Faliagas

Reputation: 636

Vlookup cells containing dates in date format, text and general format

I'm vlooking up cells that contain dates, some of which are in date format, others in text format and others in general format. I am trying to vlookup the value of the cells as it is shown.

So far, if the format of the cell is date, it returns a number, if the format of the cell is text returns the value correctly and if the value of the cell is date, it returns number, which if I manually turn it to date format, it prints the date in mm/dd/yyyy and not in dd/mm/yyyy as it is in the sheet that I vlookup it from. Is there a code to "screeshot" the value and vlookup it exactly as it is shown (as if it is a text).

This is something that I tried. It runs but still it does not solve nothing.

NEW CODE

lastrow2 = ws3.Range("F" & Rows.Count).End(xlUp).Row
Dim cell As Range
Dim CellText As String
For Each cell In ws3.Range("I2:I" & lastrow2)
    If cell.Text <> cell.Value And Len(cell.Value) <= 255 Then
        CellText = cell.Text
        cell.NumberFormat = "@"
        cell.Value = CellText
    End If
Next cell

EDIT

Examples of input data (I provide two screeshots to see how the input dates have different formats

enter image description here enter image description here

Upvotes: 0

Views: 1336

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96773

This short nacro will look for data in columns I:J that can be interpreted as a date and convert it into true dates in format "dd/mm/yyyy":

Sub DateFixer()
    Dim ws3 As Worksheet, r As Range, d As Date
    Set ws3 = ActiveSheet

    For Each r In ws3.Range("I:J").Cells.SpecialCells(2)
        arr = Split(r.Text, "/")
        If UBound(arr) = 2 Then
            d = DateSerial(arr(2), arr(1), arr(0))
            r.Clear
            r.NumberFormat = "dd/mm/yyyy"
            r.Value = d
        End If
    Next r
End Sub

Upvotes: 1

Related Questions