Is there anyway that I can have a macro insert a date into a column.
would like to the date and time to show when the macro was run.

Activecell.offset(0,5).value = (actual date and time of when macro wa
run

--
jhahe
-----------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...fo&userid=2359
View this thread: http://www.excelforum.com/showthread.php?threadid=38567

Activecell.offset(0,5).value = Format(Now,"dd mmm yyyy hh:mm:ss")

--
HTH

Bob Phillips

"bhofsetz" wrote in
message ...

Use

Activecell.offset(0,5).value = Now

HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile:
http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=385675

Is there any way to replace part of a date within a macro? I want to change
the month. The syntax is "1/1/2004" I want to have it replaced with
"2/1/2004" but the only thing I need is to update the month in all occurences
of "1/xxx/2004" Right now I am manually editing each line to change a
1/1/2004 to a 2/1/2004.

"Bob Phillips" wrote:

Activecell.offset(0,5).value = Format(Now,"dd mmm yyyy hh:mm:ss")

--
HTH

Bob Phillips

"bhofsetz" wrote in
message ...

Use

Activecell.offset(0,5).value = Now

HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile:
http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=385675

The general formula in code
could be something like:

Dim yr as Integer, mnth as Integer, dy as Integer
yr = year(ActiveCell)
mnth = month(ActiveCell)+1
dy = day(ActiveCell)

ActiveCell.Offset(0,5)=DateSerial(yr, mnth, dy)


but if mnth = 13 it must be reset to 1 and yr = yr +1

--
steveB

Remove "AYN" from email to respond
"lschuh" wrote in message
...
Is there any way to replace part of a date within a macro? I want to
change
the month. The syntax is "1/1/2004" I want to have it replaced with
"2/1/2004" but the only thing I need is to update the month in all
occurences
of "1/xxx/2004" Right now I am manually editing each line to change a
1/1/2004 to a 2/1/2004.

"Bob Phillips" wrote:

Activecell.offset(0,5).value = Format(Now,"dd mmm yyyy hh:mm:ss")

--
HTH

Bob Phillips

"bhofsetz" wrote
in
message ...

Use

Activecell.offset(0,5).value = Now

HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile:
http://www.excelforum.com/member.php...o&userid=18807
View this thread:
http://www.excelforum.com/showthread...hreadid=385675

Thank you for the code I am going to print it out. It might sound elementary
but would you place this in the macro you are changing or create a new macro
that references the macro to change?

I probably wrote 10 messages here and in MSAccess discussion groups and I
finally figured out how to replace the month with the replace function. The
only thing I have to change is the occurences where it changes not only the
month but the day to. Appreciate the help.

"STEVE BELL" wrote:

The general formula in code
could be something like:

Dim yr as Integer, mnth as Integer, dy as Integer
yr = year(ActiveCell)
mnth = month(ActiveCell)+1
dy = day(ActiveCell)

ActiveCell.Offset(0,5)=DateSerial(yr, mnth, dy)


but if mnth = 13 it must be reset to 1 and yr = yr +1

--
steveB

Remove "AYN" from email to respond
"lschuh" wrote in message
...
Is there any way to replace part of a date within a macro? I want to
change
the month. The syntax is "1/1/2004" I want to have it replaced with
"2/1/2004" but the only thing I need is to update the month in all
occurences
of "1/xxx/2004" Right now I am manually editing each line to change a
1/1/2004 to a 2/1/2004.

"Bob Phillips" wrote:

Activecell.offset(0,5).value = Format(Now,"dd mmm yyyy hh:mm:ss")

--
HTH

Bob Phillips

"bhofsetz" wrote
in
message ...

Use

Activecell.offset(0,5).value = Now

HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile:
http://www.excelforum.com/member.php...o&userid=18807
View this thread:
http://www.excelforum.com/showthread...hreadid=385675

You're very Welcome!

Put the code where it works best. The choice is always yours...

And you are finding that there are many ways to get there from here...

Making a separate module can simplify your code, make the procedure
available to any other module, and make modifying easier.

Keep on Exceling...

--
steveB

Remove "AYN" from email to respond
"lschuh" wrote in message
...
Thank you for the code I am going to print it out. It might sound
elementary
but would you place this in the macro you are changing or create a new
macro
that references the macro to change?

I probably wrote 10 messages here and in MSAccess discussion groups and I
finally figured out how to replace the month with the replace function.
The
only thing I have to change is the occurences where it changes not only
the
month but the day to. Appreciate the help.

"STEVE BELL" wrote:

The general formula in code
could be something like:

Dim yr as Integer, mnth as Integer, dy as Integer
yr = year(ActiveCell)
mnth = month(ActiveCell)+1
dy = day(ActiveCell)

ActiveCell.Offset(0,5)=DateSerial(yr, mnth, dy)


but if mnth = 13 it must be reset to 1 and yr = yr +1

--
steveB

Remove "AYN" from email to respond
"lschuh" wrote in message
...
Is there any way to replace part of a date within a macro? I want to
change
the month. The syntax is "1/1/2004" I want to have it replaced
with
"2/1/2004" but the only thing I need is to update the month in all
occurences
of "1/xxx/2004" Right now I am manually editing each line to change a
1/1/2004 to a 2/1/2004.

"Bob Phillips" wrote:

Activecell.offset(0,5).value = Format(Now,"dd mmm yyyy hh:mm:ss")

--
HTH

Bob Phillips

"bhofsetz"
wrote
in
message ...

Use

Activecell.offset(0,5).value = Now

HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile:
http://www.excelforum.com/member.php...o&userid=18807
View this thread:
http://www.excelforum.com/showthread...hreadid=385675

I ran that code. Pretty cool. Now I need to figure out what I can use it for.

"STEVE BELL" wrote:

You're very Welcome!

Put the code where it works best. The choice is always yours...

And you are finding that there are many ways to get there from here...

Making a separate module can simplify your code, make the procedure
available to any other module, and make modifying easier.

Keep on Exceling...

--
steveB

Remove "AYN" from email to respond
"lschuh" wrote in message
...
Thank you for the code I am going to print it out. It might sound
elementary
but would you place this in the macro you are changing or create a new
macro
that references the macro to change?

I probably wrote 10 messages here and in MSAccess discussion groups and I
finally figured out how to replace the month with the replace function.
The
only thing I have to change is the occurences where it changes not only
the
month but the day to. Appreciate the help.

"STEVE BELL" wrote:

The general formula in code
could be something like:

Dim yr as Integer, mnth as Integer, dy as Integer
yr = year(ActiveCell)
mnth = month(ActiveCell)+1
dy = day(ActiveCell)

ActiveCell.Offset(0,5)=DateSerial(yr, mnth, dy)


but if mnth = 13 it must be reset to 1 and yr = yr +1

--
steveB

Remove "AYN" from email to respond
"lschuh" wrote in message
...
Is there any way to replace part of a date within a macro? I want to
change
the month. The syntax is "1/1/2004" I want to have it replaced
with
"2/1/2004" but the only thing I need is to update the month in all
occurences
of "1/xxx/2004" Right now I am manually editing each line to change a
1/1/2004 to a 2/1/2004.

"Bob Phillips" wrote:

Activecell.offset(0,5).value = Format(Now,"dd mmm yyyy hh:mm:ss")

--
HTH

Bob Phillips

"bhofsetz"
wrote
in
message ...

Use

Activecell.offset(0,5).value = Now

HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile:
http://www.excelforum.com/member.php...o&userid=18807
View this thread:
http://www.excelforum.com/showthread...hreadid=385675

I just set up a form to scan through a list of dates and change the dates.
(My dates got screwed up)

The form checks each cell for the month #, day #, and year #.
Than I make selections from separate drop-downs for the revised month #, day
#, and year #
and replace the date in the cell using the DateSerial formula.

This allows me to go through a long list and correct all the dates
(especially since all the dates are previous to 2005 - thus preventing me
from just entering m/d)

The code I sent you does something similar. It looks at the date and adds
one month to it. (It also accounts for going from Dec to Jan by changing
the year. Than puts the new date into another cell.

--
steveB

Remove "AYN" from email to respond
"lschuh" wrote in message
...
I ran that code. Pretty cool. Now I need to figure out what I can use it
for.

"STEVE BELL" wrote:

You're very Welcome!

Put the code where it works best. The choice is always yours...

And you are finding that there are many ways to get there from here...

Making a separate module can simplify your code, make the procedure
available to any other module, and make modifying easier.

Keep on Exceling...

--
steveB

Remove "AYN" from email to respond
"lschuh" wrote in message
...
Thank you for the code I am going to print it out. It might sound
elementary
but would you place this in the macro you are changing or create a new
macro
that references the macro to change?

I probably wrote 10 messages here and in MSAccess discussion groups and
I
finally figured out how to replace the month with the replace function.
The
only thing I have to change is the occurences where it changes not only
the
month but the day to. Appreciate the help.

"STEVE BELL" wrote:

The general formula in code
could be something like:

Dim yr as Integer, mnth as Integer, dy as Integer
yr = year(ActiveCell)
mnth = month(ActiveCell)+1
dy = day(ActiveCell)

ActiveCell.Offset(0,5)=DateSerial(yr, mnth, dy)


but if mnth = 13 it must be reset to 1 and yr = yr +1

--
steveB

Remove "AYN" from email to respond
"lschuh" wrote in message
...
Is there any way to replace part of a date within a macro? I want
to
change
the month. The syntax is "1/1/2004" I want to have it
replaced
with
"2/1/2004" but the only thing I need is to update the month in all
occurences
of "1/xxx/2004" Right now I am manually editing each line to change
a
1/1/2004 to a 2/1/2004.

"Bob Phillips" wrote:

Activecell.offset(0,5).value = Format(Now,"dd mmm yyyy hh:mm:ss")

--
HTH

Bob Phillips

"bhofsetz"
wrote
in
message
...

Use

Activecell.offset(0,5).value = Now

HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile:
http://www.excelforum.com/member.php...o&userid=18807
View this thread:
http://www.excelforum.com/showthread...hreadid=385675