Results 1 to 2 of 2
11-16-11, 09:24 #1Registered User
- Join Date
- Nov 2011
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
Dim Column As Variant
Dim Rounddown As Double
If Column = Start_Date Then
WorksheetFunction.roundown = Rounddown(D, 0)
Any help/adivse would be very much appreciated
01-05-12, 14:28 #2Registered User
- Join Date
- Jan 2002
- Bay Area
This might help if you have not solved it yet.
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
Last edited by JerryDal; 01-05-12 at 14:32.