Consulting

Results 1 to 8 of 8

Thread: How to create a macro that puts today's date and time in cell

  1. #1

    How to create a macro that puts today's date and time in cell

    Hi All

    I'm new at VBA and just started playing around with it...
    I am trying to create a button with a Macro behind it that once pushed it populates the selected cell with today's date and then jumps to another cell

    here is the scenario:

    Button pushed-->Today's date & time in Cell A1-->jumps to B1
    Button pushed -->Today's date & time in Cell B1-->jumps to A2
    Button pushed -->Today's date & time in Cell A2-->jumps to B2
    Button pushed -->Today's date & time in Cell B2-->jumps to C1
    ......

    Can anyone help with that?

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    That logic seems odd. Why B1 -> A2, but B2 -> C1?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    One way.
    Sub Maybe()
    Select Case ActiveCell.Address
        Case Is = Cells(1, 1).Address
            ActiveCell.Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
                Cells(1, 2).Select
        Case Is = Cells(1, 2).Address
            ActiveCell.Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
                Cells(2, 1).Select
        Case Is = Cells(2, 1).Address
            ActiveCell.Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
                Cells(2, 2).Select
        Case Is = Cells(2, 2).Address
            ActiveCell.Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
                Cells(1, 3).Select
        Case Else
    End Select
    End Sub
    Last edited by jolivanes; 10-12-2020 at 05:15 PM. Reason: Keep Consistent

  4. #4
    Thanks Jolivans this works...but I had it wrong (sorry about that...)

    Let me explain in length...I am creating a simple "observation" tool to watch someone and record his action so I created a simple table that user need to insert the start time and end time for each action - see pic below
    the "button" needs to have a macro assigned to it that does the below

    So I got it wrong above...it should jump to the next line and not column and do that endlessly

    Button pushed-->Today's date & time in Cell A1-->jumps to B1
    Button pushed -->Today's date & time in Cell B1-->jumps to A2
    Button pushed -->Today's date & time in Cell A2-->jumps to B2
    Button pushed -->Today's date & time in Cell B2-->jumps to A3 (not C1)

    and onwards...

    hope it's clearer nowex.jpg

  5. #5
    BTW - the macro above only does the first row (meaning populates only A1 & B1...then it jumps to C1 instead of A2

  6. #6
    There must be a better way but for now it'll do. It's past my bedtime!
    Sub Maybe_2()
    If ActiveCell.Row Mod 2 <> 0 And ActiveCell.Column Mod 2 <> 0 Then ActiveCell.Value = Format(Now, "mm/dd/yyyy HH:mm:ss"): ActiveCell.Offset(, 1).Select: Exit Sub
    If ActiveCell.Row Mod 2 <> 0 And ActiveCell.Column Mod 2 = 0 Then ActiveCell.Value = Format(Now, "mm/dd/yyyy HH:mm:ss"): ActiveCell.Offset(1, -1).Select: Exit Sub
    If ActiveCell.Row Mod 2 = 0 And ActiveCell.Column Mod 2 <> 0 Then ActiveCell.Value = Format(Now, "mm/dd/yyyy HH:mm:ss"): ActiveCell.Offset(, 1).Select: Exit Sub
    If ActiveCell.Row Mod 2 = 0 And ActiveCell.Column Mod 2 = 0 Then ActiveCell.Value = Format(Now, "mm/dd/yyyy HH:mm:ss"): ActiveCell.Offset(1, -1).Select: Exit Sub
    End Sub

  7. #7
    Thanks that work great !!!
    Good night

  8. #8
    This should be sufficient
    Sub Maybe_4()
    If ActiveCell.Column = 1 Then
        ActiveCell.Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
        ActiveCell.Offset(, 1).Select
    Else
        ActiveCell.Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
        ActiveCell.Offset(1, -1).Select
    End If
    End Sub
    or even this
    Sub Maybe_3()
    Select Case ActiveCell.Column
        Case Is = 1
            ActiveCell.Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
                ActiveCell.Offset(, 1).Select
        Case Else
            ActiveCell.Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
                ActiveCell.Offset(1, -1).Select
    End Select
    End Sub

Posting Permissions

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