Date format "dd / mm / yyyy" in excel via vba

Question

I am trying to write a date in the format "dd / mm / yyyy" on an excel sheet via excel-vba. I achieved this using Cells(1, 1).Value = Format(StartDate, "dd/mm/yyyy"). and my cell value is 30/04/2014that good .....

But there is some strange problem ... Since I have to iterate startDate throughout the month, adding it to 1 each time, so the next value will be 1/5/2014instead of 01/05/2014until the 12th day of every month 12/5/2014and from the 13th, the month changes again to two digits 13/05/2014....

I want all this to consist of two digits, since I need to search for these dates again using the Range.Find method, in which I pass the value using "dd / mm / yyyy" fromat.

Answer 1

I recieved it

Cells (1, 1) .Value = StartDate
Cells (1, 1) .NumberFormat = "dd / mm / yyyy"

Basically, I need to set the cell format, not set the date.

Answer 2

Your problem is trying to change the month by adding 1. 1 to the date series in Excel equal to 1 day. Try changing the month using the following:

NewDate = Format(DateAdd("m",1,StartDate),"dd/mm/yyyy")