creating macros to auto fill cells
 

I am trying to create macros that will auto fill cells. I have made it
ok to the point of inserting new row and filling in the info i want
but in the column where i have the date it keeps changing all the
previous entries to the current entries value. It happens only in this
column with the date. I am using the value =now() , is there another
value i can enter to correct this? Thanks ,,Dev

creating macros to auto fill cells
 

dev,

Not sure I understand, but the following will convert all the formulas in
column 1 to values:

Columns(1) = Columns(1).Value

If you apply this after you insert your new row with the Now() formula, then
it will "freeze" the date and time. If you don't want to do the whole
column, you could use the same type of syntax with a range:

range("A1") = range("A1")

hth,

Doug

"dev" wrote in message
om...
I am trying to create macros that will auto fill cells. I have made it
ok to the point of inserting new row and filling in the info i want
but in the column where i have the date it keeps changing all the
previous entries to the current entries value. It happens only in this
column with the date. I am using the value =now() , is there another
value i can enter to correct this? Thanks ,,Dev

creating macros to auto fill cells
 

"Doug Glancy" wrote in message ...
dev,

Not sure I understand, but the following will convert all the formulas in
column 1 to values:

Columns(1) = Columns(1).Value

If you apply this after you insert your new row with the Now() formula, then
it will "freeze" the date and time. If you don't want to do the whole
column, you could use the same type of syntax with a range:

range("A1") = range("A1")

hth,

Doug

That did it, thanks Doug.

creating macros to auto fill cells
 

H

I just came across this and wanted to ask about a sheet I am trying to prepare, how do I apply the following you mentioned

"Columns(1) = Columns(1).Value

we have a sheet here that we are using the NOW() function to add the time and date that an entry is made, however every time a new entry is made on another row it affects every time and date recorded in the time/date column to change with it!

Where exactly in excel do I apply this "Columns(1) = Columns(1).Value" that you mention

Regard

Ax

----- Doug Glancy wrote: ----

dev

Not sure I understand, but the following will convert all the formulas i
column 1 to values

Columns(1) = Columns(1).Valu

If you apply this after you insert your new row with the Now() formula, the
it will "freeze" the date and time. If you don't want to do the whol
column, you could use the same type of syntax with a range

range("A1") = range("A1"

hth

Dou

"dev" wrote in messag
om..
I am trying to create macros that will auto fill cells. I have made i
ok to the point of inserting new row and filling in the info i wan
but in the column where i have the date it keeps changing all th
previous entries to the current entries value. It happens only in thi
column with the date. I am using the value =now() , is there anothe
value i can enter to correct this? Thanks ,,De

creating macros to auto fill cells
 

Right click on the sheet tab and select view code

At the top of the module
In the left dropdown select Worksheet
in the right dropdown select Change (not selection change)

this will put in a declaration for the change event.

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

This event will fire everytime you complete the edit of a cell. So in this
routine you would test for the cell being changed that required a timestamp.
Assume a change in column B requires a timestamp in column A of the same row
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Column = 2 Then
Application.EnableEvents = False
If Not IsEmpty(Target) Then
Cells(Target.Row, 1).Value = Now
Cells(Target.Row, 1).NumberFormat = "mm/dd/yyyy hh:mm"
Else
Cells(Target.Row, 1).ClearContents
End If
End If

ErrHandler:
Application.EnableEvents = True
End Sub


--
Regards,
Tom Ogilvy



"Axeman" wrote in message
...

Hi

I just came across this and wanted to ask about a sheet I am trying to
prepare, how do I apply the following you mentioned

"Columns(1) = Columns(1).Value"

we have a sheet here that we are using the NOW() function to add the time
and date that an entry is made, however every time a new entry is made on
another row it affects every time and date recorded in the time/date column
to change with it!.

Where exactly in excel do I apply this "Columns(1) = Columns(1).Value"
that you mention?

Regards

Axe

----- Doug Glancy wrote: -----

dev,

Not sure I understand, but the following will convert all the
formulas in
column 1 to values:

Columns(1) = Columns(1).Value

If you apply this after you insert your new row with the Now()
formula, then
it will "freeze" the date and time. If you don't want to do the
whole
column, you could use the same type of syntax with a range:

range("A1") = range("A1")

hth,

Doug

"dev" wrote in message
om...
I am trying to create macros that will auto fill cells. I have made
it
ok to the point of inserting new row and filling in the info i want
but in the column where i have the date it keeps changing all the
previous entries to the current entries value. It happens only in
this
column with the date. I am using the value =now() , is there
another
value i can enter to correct this? Thanks ,,Dev