Using RoundDown in VBA on a specific column/range of fields
I am currently try (unsuccessfully) to write macro (which can become part of a macro I am writing in MS Project - so round down a column of dates/times so 16/11/2011 13:14 would become 16/11/2011 00:00. The formula I would use in excel would be =Rounddown (D7,0) where column D is Start_Date and 7 would be a cell in part of the range.
The set up is Start_Date will always be column D but the range in which D2 onwards so could be variable, what I want to do is right a bit of VB code that will automatically rounddown all of these field, once I have this working in excel I would then transfer this to the MS project macro as it exports dates into an excel spreadsheet.
Here is what I have so far, but errors out and I am unsure how to do it correctly
This might help if you have not solved it yet.
'Date/Time values in column D rounded down
'so that hh:mm appears in each cell as 00:00
Dim i As Long
Dim ws As Worksheet, tempDate As Variant
Set ws = Sheets("Sheet1")
i = 2
Do While .Cells(i, 4).Value <> ""
tempDate = .Cells(i, 4).Value
.Cells(i, 4).Value = _
.Cells(i, 4).NumberFormat = "mm/dd/yyyy hh:mm"
i = i + 1
Set ws = Nothing