VBA to move current date into cell

[Shadkng]

Hi, I am having trouble moving the current date into a cell. When I use the below line I am getting a funny date, 4/45/27. When I manually change the format to date then the it corrects.

Worksheets("COPYORD").Range("D" & y).Value = Date

The I added the below line to change the format, but I got a "false" return.

Worksheets("COPYORD").Range("D" & y).Value = NumberFormat = "dd-mm-yy"

I have been searching online for an answer, but I must be missing something.

Thanks

 

[Rick Rothstein]

Your first code line is fine (assuming there is a sheet named COPYORD or that the variable y has been assigned a value. I have no explanation as to the value you showed us. I would suggest creating a new workbook, rename a sheet to COPYORD and then try your code in that new workbook and see if it works correctly or not.

As to your second code line, that syntax is incorrect. NumberFormat is a property of the Range object so you don't assign it to the Range's Value, That code line should look like this...

Worksheets("COPYORD").Range("D" & y).NumberFormat = "dd-mm-yy"

 

[Shadkng]

Your first code line is fine (assuming there is a sheet named COPYORD or that the variable y has been assigned a value. I have no explanation as to the value you showed us. I would suggest creating a new workbook, rename a sheet to COPYORD and then try your code in that new workbook and see if it works correctly or not.

As to your second code line, that syntax is incorrect. NumberFormat is a property of the Range object so you don't assign it to the Range's Value, That code line should look like this...

Worksheets("COPYORD").Range("D" & y).NumberFormat = "dd-mm-yy"

OK, so I took the variable out of the equation and I still get the wrong date. If I change the cell to D1, it works correctly. I guess there could be something elsewhere in the code changing that cell format.

Worksheets("COPYORD").Range("D5").Value = Date
        Worksheets("COPYORD").Range("D5").NumberFormat = "dd-mm-yy"

 

[Rick Rothstein]

After the cell gets the wrong date, look up the format for the cell... look up the Custom format and write back to us what it says there.