If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > PC based Database Applications > Microsoft Excel > Using RoundDown in VBA on a specific column/range of fields

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Nov 2011
Posts: 1
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)

End If

End Sub

Any help/adivse would be very much appreciated
Reply With Quote
  #2 (permalink)  
Registered User
Join Date: Jan 2002
Location: Bay Area
Posts: 507
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
    End With

    Set ws = Nothing
End Sub

Last edited by JerryDal; 01-05-12 at 13:32.
Reply With Quote

excel, rounddown, vba

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On