creating macros to auto fill cells

[dev]

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

 

[Doug Glancy]

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

 

[Guest]

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

 

[Tom Ogilvy]

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

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!.