VBA - Date don't recognize itself as a Date (keeps left alignment until I click it)

[Flinker]

Hi everyone,

I m trying to do a simple VLOOKUP formula with two different Dates. One of the Dates come from other Excel Document (Col A) and the other one come from a HTMLDocument (Col B).
Both columns have Date Format but column B don't recognize itself as a Date until I double click manually a cell, so it go from left alignment to right alignment. When I do a VLOOKUP between both columns, it only works when I've double clicked the cell in column B:

I've tried to modify the format of column B just before I paste the date from the HTMLDocument using this VBA formula:

ThisWorkbook.Worksheets("hoja").Range("B:B").NumberFormat = "dd/mm/yyyy"

But this is changing all the value of the dates cause it having problems with dd/mm/yyyy and mm/dd/yyyy. In this picture the dates should be from the first business day of the year but its changing 02/01/2023 (dd/mm/yyyy) to 01/02/2023 (dd/mm/yyyy) and so on:

How can I change the format of column B with VBA to make my VLOOKUP work in format "dd/mm/yyyy"?

I tried google but no success. I'm new here so thanks in advance.
Regards

 

[kevin9999]

Try running the following code & see if your Vlookup formulas work afterwards.

Option Explicit
Sub GetDates()
    Dim ws As Worksheet, LRow As Long, i As Long
    Set ws = Worksheets("Sheet1")               '<~ change to actual sheet name
    LRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
    
    For i = 2 To LRow
        ws.Cells(i, 2).Value = DateValue(ws.Cells(i, 2))
    Next i
End Sub

 

[Flinker]

Thats worked for me! Thanks a lot kevin9999, I've been dealing with this for a few days.

Regards

 

[Flinker]

Sorry I cant edit the post from before and I just figured out that is not working as i thought.
Now its like a Date with Right alignment but its not working with "dd/mm/yyyy/" format. 01/02/2023 should be 02/01/2023 (2th january) but its working fine with 13/01/2023.

I've tried something like this but still having problem with format (Its getting the some output like in the 2nd picture):

ws.Cells(i, 1).Value = Format(DateValue(ws.Cells(i, 1)), "dd/mm/yyyy")

Thanks you

 

[MARK858]

With your original data as per post 1 try selecting column B then

Data tab - Text to Columns - click Next - Make sure all the boxes are empty and unchecked - click Next - click the Date checkbox - choose the DMY option - click Finish.

Format the column as desired.
Does it fix the issue?

 

[Flinker]

Thanks for answering Mark858. Yep That's working but I need to do it with VBA macro not manually.
Thanks you.

 

[kevin9999]

It's hard to see just what's happening from an image - could you post a sample of your data using the XL2BB add in? Or better still, share your file via Google Drive, Dropbox or some other file-sharing site?

 

[MARK858]

Thanks for answering Mark858. Yep That's working but I need to do it with VBA macro not manually.
Thanks you.

You need to learn how to use the macro recorder anyway....

    Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True