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

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2002
Location: Bay Area
Posts: 505
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 13:32.
Reply With Quote
Reply

Tags
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