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

[marmoor99]

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?

[Bob Phillips]

That logic seems odd. Why B1 -> A2, but B2 -> C1?

[jolivanes]

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

[marmoor99]

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 now27300

[marmoor99]

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

[jolivanes]

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

[marmoor99]

Thanks that work great !!!
Good night:sleep2:

[jolivanes]

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