Results 1 to 4 of 4

Thread: date macro

  1. #1
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224

    Unanswered: date macro

    Could some one write me a macro to add 6 months to the date entered in a cell
    Thank you
    eg : 1/1/2005 should become 7/1/2005

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    That can be easy, but depending on what you want to do with it, it might be better to have one cell as entry, and the other that will automatically add six months. That can be done by formula rather than VBA.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Dec 2004
    Posts
    78
    Use this, combines VBA and excel functionalities:
    Code:
    Dim i as Integer
    Sub test()
    i = 1
    For i = 1 To 10 'Rows 1 to 10
        Range("B" & i).Select    'A column with old date, B column with new date
        dateStr = "=date(year(A" & i & "),month(A" & i & ")+6,day(A" & i & "))"
        ActiveCell.Value = dateStr
    Next
    End Sub
    ~BS

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    heres my attempt at a macro
    Code:
    Sub Dateaddition()
        Dim LastCell    As Range
        Dim rngUse      As Range
        Dim cl          As Range
        
        'replacing the current cells value with an additional 6 months
        Set LastCell = Cells.Find("*", , , , , xlPrevious)
        If Not LastCell Is Nothing Then
            Set rngUse = Range(Cells(1, 1), Cells(LastCell.Row, 1))
                For Each cl In rngUse
                    'remove the offset to overwrite the values
                    If IsDate(cl.Value) Then _
                        cl.Offset(0, 1).Formula = DateAdd("m", 6, cl.Value)
                Next cl
        End If
        
        'get rid of object variables
        Set cl = Nothing
        Set rngUse = Nothing
        Set LastCell = Nothing
    End Sub
    this can be easily modified to work on any range of cells you want
    i have just done all the cells in column A as an example
    but as shades suggests if you can just do this by formula
    Dave

Posting Permissions

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