Using RoundDown in VBA on a specific column/range of fields

thed85 · #1 (**permalink**) 11-16-11, 08:24

Hi,

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

Dim Column As Variant
Dim Rounddown As Double

If Column = Start_Date Then

WorksheetFunction.roundown = Rounddown(D, 0)

End If

End Sub

Any help/adivse would be very much appreciated

JerryDal · #2 (**permalink**) 01-05-12, 13:28

This might help if you have not solved it yet.
Jerry

Code:

Sub Macro1()
'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")
    
    With Sheets(ws.Name)
        i = 2
        Do While .Cells(i, 4).Value <> ""
            tempDate = .Cells(i, 4).Value
            .Cells(i, 4).Value = _
                WorksheetFunction.RoundDown(tempDate, 0)
            .Cells(i, 4).NumberFormat = "mm/dd/yyyy hh:mm"
            i = i + 1
        Loop
    
    End With

    Set ws = Nothing
    
End Sub

Internet Services

Web Development

Digital Marketing

Consumer Tech