Insert new row with incremental formula

[MartinCosk]

Hello,

My worksheet is an event log, which allows the user to record events and put a permanent date/time stamp on the entry. I am using a slightly modified widely available code to insert a new empty row below the last entry. The time stamp occurs when the user enters the data, not when a new row (new event) is selected. When a new event is selected, the code copies the last row, and then pastes the formatting plus the formulas in cells B and C to ensure the time stamp takes place.

This is working fine but on occasion there may be some catch up on an event that occurred earlier and a manual entry on the time is needed (cell B), which of course deletes the formula. When a new event is selected, the new cell B contains the manually entered time, not the formula required to stamp the time. I understand how to code to put a specific formula into a specific cell but adding this to a new row with increments to match the row number is causing me a problem.

So, I am after some help to insert an incremental formula into a specific cell in a new row each time the new row is created. The next step will be to have the data sorted in time order, either on a new row or enter but that is probably next weekend’s task….

The worksheet layout is
Cell B = Time
Cell C = Date
Cell D = event text
Formula for time/date stamp is =IF(D12<>"",IF(B12<>"",B12,NOW()),""). (I have code in the workbook page to enable iterative calculations on opening)

Many thanks for any help and welcome any comments if there is a need to make the code more efficient.

Sub Insert_New_Rows()
    
    Dim Lr As Integer
    Lr = Range("B" & Rows.Count).End(xlUp).Row 'Searching last row in column B
    Rows(Lr + 1).Insert Shift:=xlDown 'Inserting new row
    Rows(Lr).Copy 'Copying format of last row
    Rows(Lr + 1).PasteSpecial Paste:=xlPasteFormats 'Pasting format to new row
    Rows(Lr + 1).PasteSpecial Paste:=xlPasteFormulas ' paste formulas for time / date stamps into new row
    Rows(Lr + 1).SpecialCells(xlConstants).ClearContents 'clear contents for the new row
    Range("B" & ActiveCell.Row & ":C" & ActiveCell.Row).Copy
    Application.CutCopyMode = False 'Deactivating copy mode

End Sub

 

[Joe4]

Welcome to the Board!

Which column are you putting this time stamp in?

 

[MartinCosk]

Welcome to the Board!

Which column are you putting this time stamp in?

Thank you. Just re-read my question and it should say the time stamps are in columns B and C. Hope that makes it clearer...

 

[Joe4]

Note that this formula will NOT give you a Timestamp:

=IF(D12<>"",IF(B12<>"",B12,NOW()),"")

NOW() will ALWAYS return the current date and time.
So when you open it tomorrow, all the old records that have this formula will show the current date/time, not the date/time when the record was added/updated.

If you want a "frozen in time" date/time stamp, you will need to have VBA hard-code that value in there, and not copy a formula, which will always return the current date/time.

 

[MartinCosk]

Hi. Thanks for the reply.

Just to clarify, this formula does provide me with a "frozen in time" time and date stamp. This occurs as it is a circular reference with worksheet permission to enable iterative calculations (coded to enable on opening of the workbook). This is all working fine.

I am looking for a way to pasted this formula into the next empty cell in columns B & C with the incremental change to match the new row number ie,

=IF(D12<>"",IF(B12<>"",B12,NOW()),"")
=IF(D13<>"",IF(B13<>"",B13,NOW()),"")
=IF(D14<>"",IF(B14<>"",B14,NOW()),"")

etc etc

 

[Joe4]

OK, it wasn't quite clear what cell/column you were putting this formula into.

You can get the code you need by turning on the Macro Recorder and recording yourself manually entering the formula into one of the cells.
So, if you were to turn on the Macro Recorder and record yourself manually entering:

=IF(D12<>"",IF(B12<>"",B12,NOW()),"")

into cell B12, you would get this:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B12").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[2]<>"""",IF(RC<>"""",RC,NOW()),"""")"
    Range("B12").Select
End Sub

The part in red is the important part. Since the formula is using relative referencing, we do not need to worry about row numbers in the formula. It will be relative to whatever row we put it in.
So, if we wanted to put it in the next row in column B (LR+1), we would want to use this line of code:

Range("B" & LR + 1).FormulaR1C1 = "=IF(RC[2]<>"""",IF(RC<>"""",RC,NOW()),"""")"

If you have to do the same thing for column C, just repeat the same process I outlined above.

 

[MartinCosk]

Hi, many thanks for the follow up. I'd gone through the record macro routine but I had pasted the formula into the cell rather than typed it out, which explains why I wasn't getting anything as starting point. Feel a bit foolish on that one, so thank you for that lesson - every day is a learning event!!

I needed to amend the code slightly so that it fully reflected what I wanted it to do, I realized that in the formula I provided should have been =IF($D12<>"",IF(B12<>"",B12,NOW()),""), with the absolute reference on column D. The final code is:

Range("B" & Lr + 1).FormulaR1C1 = "=if(RC4<>"""",if(RC<>"""",RC,now()),"""")"

Thank you for your help with this. Working like a charm now!

 

[Joe4]

You are welcome.

Hi, many thanks for the follow up. I'd gone through the record macro routine but I had pasted the formula into the cell rather than typed it out, which explains why I wasn't getting anything as starting point. Feel a bit foolish on that one, so thank you for that lesson - every day is a learning event!!

Note if the formula already exists in the cell, you don't actually need to manually re-type it. You can just turn on your Macro Recorder, select the cell and press F2 to go into Edit mode, and then click enter to "re-enter" the formula. Then the Macro Recorder will capture that whole formula without you having to re-type it again.

 

[Sheik]

Instead of rewriting the formula, you alo could just copy an existing cell, that still has the formula.
So to copy always the last row generates the problem here.
Better always copy a dedicated row that still has the formulas for sure.