Simple Date/Time Macro


T

Tom Hall

ActiveCell.FormulaR1C1 = "9/16/2009"
Range("B1").Select
ActiveCell.FormulaR1C1 = "9:25 AM"
Range("C1").Select
End Sub

I want to set up a simple workbook that enters the above information on the
current line in the sheet. When I recorded the keystrokes I wanted, I ended
up with the following macro, which only works once.

Starting with the second line, running the macro puts the date in the right
place, skips Column B and then leaves the cursor in R1C1.

How do I get this macro to enter all the information on the current line
(date in column 1, time in column 2)?

TIA,

Tom
 
Ad

Advertisements

D

Dave Peterson

I'd put the date and time in the same cell. It should make date/time arithmetic
a little easier:

With activecell.entirerow.cells(1) 'column A of the row with the activecell
.numberformat = "mm/dd/yyyy hh:mm:ss"
.value = now
end with
 
G

Gord Dibben

Sub NOWTIME()
ActiveCell.Value = Format(Now, "mm/dd/yyyy")
ActiveCell.Offset(0, 1).Value = Format(Now, "h:mm:ss AM/PM")
End Sub

You can also combine this into one cell if you wanted.


Gord Dibben MS Excel MVP
 
T

Tom Hall

Sub NOWTIME()
ActiveCell.Value = Format(Now, "mm/dd/yyyy")
ActiveCell.Offset(0, 1).Value = Format(Now, "h:mm:ss AM/PM")
End Sub

You can also combine this into one cell if you wanted.

Thanks, Gord (and the others who responded)!

This does exactly what I was looking for. I was able to figure out how to
get the cursor to end up in column 3 after inserting the date and time.

Sub NOWTIME()
ActiveCell.Value = Format(Now, "mm/dd/yyyy")
ActiveCell.Offset(0, 1).Value = Format(Now, "h:mm:ss AM/PM")
ActiveCell.Offset(0, 2).Select
End Sub

An interesting note: I changed the time format string to "h:mm AM/PM" but I
still get output that includes seconds, but the value is always ":00"


Tom
 
G

Gord Dibben

I can only guess that since you do not include the seconds in the format
Excel ignores them.


Gord
 
Ad

Advertisements

D

Dave Peterson

Depending on your windows regional setting, this could result in a wrong date.

ActiveCell.Value = Format(Now, "mm/dd/yyyy")

If the date is ambiguous (like 11/12/2009), then even though you've formatted
the string in mdy order, excel will see the entry as a date and use the regional
settings to parse the entry. And if the user has a date order of dmy, that user
would see a date of December 11, 2009.

I'd use:

Sub NOWTIME()
with ActiveCell
.numberformat = "mm/dd/yyyy"
.Value = date 'not Now--since you don't want the time in the cell
with .offset(0,1)
.numberformat = "h:mm AM/PM"
.value = Time
end with
end with
End Sub

And since the .value include the seconds, the cell will include the seconds--but
the numberformat will hide it.
 
D

Dave Peterson

That formula will recalculate each time excel recalcs. You'd have to change it
a value after you enter the formula.

The original poster wanted the date in one cell and the time in another.




Why not do it easy: =TODAY() and enter it directly into the cell
you want it in?
 
G

Gord Dibben

Perhaps Tom wants a static date and time?

Perhaps the macro method is part of a grand plan?

But there are easier methods of entering a static date or time.

CTRL + ; or SHIFT + CTRL + ;


Gord

Why not do it easy: =TODAY() and enter it directly into the cell
you want it in?
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Modify range in VBA 9
insert a line 1
adding a semicolon 4
Macro Stoped Working 2
Range Commands in Macros 4
Dates in a macro 4
Macro takes forever - didn't used to 5
Filename Problem In a Macro 4

Top