Change month into date

[Panoos64]

Hi all, Kindly require to provide me support so that to create a VBA code, by which should change in col. "A" the format of mm/yyyy into dd/mm/yyyy.

e.g. 09/2018 should be 30/09/2018
10/2018 should be 31/10/2018

Thank you all in advance

 

[nemmi69]

Try this

Option Explicit
Dim LastRowNo As Long
Dim DayLoop As Long


Sub DaysInMonth()
LastRowNo = Sheets(1).Range("A1048576").End(xlUp).Row


For DayLoop = 1 To LastRowNo
    Sheets(1).Range("B" & DayLoop).Value = Format(Day(DateSerial(Year(Sheets(1).Range("A" & DayLoop).Value), Month(Sheets(1).Range("A" & DayLoop).Value) + 1, 1) - 1) & "/" & Month(Sheets(1).Range("A" & DayLoop).Value) & "/" & Year(Sheets(1).Range("A" & DayLoop).Value), "dd/mm/yyyy")
    
Next DayLoop


End Sub

 

[Panoos64]

Hi nemm, your code works perfect and based to my data! many thanks for your support. Hv a nice day