Macro to populate dates
Attend Excelapalooza
Thanks Thanks:  0
Results 1 to 10 of 10

Thread: Macro to populate dates

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to populate dates

    Hi all - hoping someone can help please.
    I need a macro that will auto populate date when adjacent cell complete as follows:-

    AMOUNT column DATE column
    E D
    H G
    J I
    M L
    Thanks in advance for any for any help

  2. #2
    Board Regular
    Join Date
    Mar 2018
    Posts
    146
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to populate dates

    Hi,

    What is the criteria that will determine what date to write in the cell ?
    What is the logic of the macro ?
    Last edited by louisH; Mar 19th, 2018 at 05:00 AM.

  3. #3
    New Member
    Join Date
    Mar 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to populate dates

    hi - I need a date to enter whenever any value is entered - the spead sheets are large and this ensures the dates of monies paid are inserted correctly and also allows me to identify mistakes.
    the value of the cell can be anything greater than 0

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    11,698
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Macro to populate dates

    So your wanting today's date entered into column B any time any value is entered into column A. Is that what you want.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  5. #5
    New Member
    Join Date
    Mar 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to populate dates

    AMOUNT DATE
    E D
    H G
    J I
    M L

  6. #6
    New Member
    Join Date
    Mar 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to populate dates

    I need todays date entered but to stay that date for the following columns
    AMOUNT DATE
    E D
    H G
    J I
    M L

  7. #7
    Board Regular
    Join Date
    Mar 2018
    Posts
    146
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to populate dates

    Hi,

    From what I understand, when someone writes Something in column A "Amount", it populates column B "Date". This is easy but the question is :
    What date ? Today ? Tomorrow ?

    Here's the code to populate col B with today's date when A is changed :
    (Code to add in the Worksheet module).

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        
        If Target.Columns.Count > 1 Or Target.Column <> 1 Then Exit Sub
        
        Dim cel As Range
        
        For Each cel In Target
            
            If cel.Value > 0 and WorksheetFunction.IsNumber(cel.Value) Then
                cel.Offset(0, 1).Value = Date
            End If
            
        Next cel
    
    End Sub
    Last edited by louisH; Mar 19th, 2018 at 05:30 AM.

  8. #8
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    11,698
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Macro to populate dates

    So if the answer to my question is yes then try this.

    This is an auto sheet event script
    Your Workbook must be Macro enabled
    To install this code:
    Right-click on the sheet tab
    Select View Code from the pop-up context menu
    Paste the code in the VBA edit window

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Modified 3-19-18 5:30 AM EDT
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    Target.Offset(, 1).Value = Date
    End If
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    11,698
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Macro to populate dates

    Try this:
    Install as mentioned in previous post. This will work for columns E,H,J,M
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Modified 3-19-18 6:00 AM EDT
    If Target.Column = 5 Or Target.Column = 8 Or Target.Column = 10 Or Target.Column = 13 Then
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    Target.Offset(, -1).Value = Date
    End If
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  10. #10
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    36,371
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Macro to populate dates

    ††
    @ lou78
    Welcome to the MrExcel board!

    If your question has not been fully resolved by any of the suggestions so far, could you clarify the following questions?

    1. If an amount is entered in an Amount column (& a date is entered in the relevant Date column) & subsequently the amount is changed, can you confirm that the Date column should be updated to the date the amount was changed?

    2. If an amount is entered in an Amount column (& a date is entered in the relevant Date column) & subsequently the amount is deleted, should the Date be
    a) left as it was?
    b) updated to the date the deletion was made?
    c) removed from the Date column?

    3. Is it ever possible that multiple amounts could be entered at once (eg copy/paste), altered at once (eg entered with Ctrl+Enter) or deleted at once?
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com