Change Numeric to Date - Excel VBA

[Binaxxx]

Hi,

I am currently having an issue, where I run a macro which for some reason I don't know is converting some of the date to US format and keeping the others to the UK format.

I am trying to find a solution to convert the US ones to the UK format based on the fact that the US ones are stored as "Numeric". But I am failing to do so

Please find attached the workbook I am using and here is the codes :

Sub TestDate()


Dim poDic As Object
Dim poArr
Dim cel As Range

Set poDic = CreateObject("Scripting.Dictionary")

With Sheets("Sheet2")
poArr = .Range("A2:E" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With

'Look for ETA

For e = 1 To UBound(poArr)
poDic(poArr(e, 1) & "," & poArr(e, 2)) = poArr(e, 5)
Next

With Sheets("Sheet1")
For Each cel2 In .Range("A2:A" & .Cells(Rows.Count, 2).End(xlUp).Row).Cells
cel2.Offset(, 10) = poDic(cel2.Value & "," & cel2.Offset(, 1).Value)


Next

End With

Dim StrDt As String, rngDt As Range
For Each rngDt In Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
If IsNumeric(rngDt) = True Then
rngDt = Date
rngDt.NumberFormat = "mm/dd/yyyy"

End If
Next
End Sub

 

[NARAYANK991]

Hi ,

The following line of code is not returning TRUE.

If IsNumeric(rngDt) = True Then

Change it to :

If IsDate(rngDt) = True Then

However , leaving this aside , why are you replacing every date in column K by today's date , through the following line of code ?

rngDt = Date

Narayan

 

[chirayu]

try this

Sub DateChanger()

Dim rng As Range
Dim subrng As Range
Dim lstrow As Long

lstrow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("K2:K" & lstrow)

For Each subrng In rng
    subrng = DateValue(subrng)
Next subrng

End Sub