Vlookup range returns date format for few rows and general format for few rows despite the source being same

Question

I am writing a code to vlookup dates for diff numbers. But it returns date for few rows and few other rows are in general format.

For the registration date, few rows are in general format (those that are visible) and few in date format (those that are as #######). Also the ones in date format have their days and months swapped. Like 09-01-2024 is entered as 01-09-2024.

The source format for all rows is exactly the same.

I cannot trace why vba is treating some entries differently.

Tried datevalue, tried changing the source to all date format, tired cdate function, tried text function...nothing worked

Answer 1

This is the code:

L = ThisWorkbook.Sheets("Basefile").Cells(Rows.Count, 2).End(xlUp).Row For i = 2 To L ThisWorkbook.Sheets("Basefile").Range("H" & i).Value = Application.VLookup(ThisWorkbook.Sheets("Basefile").Range("B" & i).Value, S1.Range("B2:AAX40000"), 43, False) Next i