Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011
    Posts
    1

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

    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

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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
    Last edited by JerryDal; 01-05-12 at 14:32.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •