Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Fill Cells | Excel Discussion (Misc queries) | |||
Creating a drop down list that will fill in mutilple cells.... | Excel Discussion (Misc queries) | |||
Macros fill cells regardless of row number | Excel Discussion (Misc queries) | |||
Creating a macros to copy and paste cells | Excel Discussion (Misc queries) | |||
Creating an auto fill or drop down | Excel Discussion (Misc queries) |