Using Add Row and iterative date function

**kmiss** · 09-28-2021, 09:25 PM

Hi All,

I am having a run time error when running the below script

Sub CommandButton1_Click()

Sheets("Sheet1").Range("A4").Select
ActiveCell.EntireRow.Insert Shift:=xlUp

Sheets("Sheet1").Range("A4:C4").Select
Selection.Borders.Weight = xlThin

Sheets("Sheet1").Range("A4").Select
ActiveCell.Formula = "=IF(B3<>"",IF(A3="",NOW(),A3),"")"

End Sub

I am trying to get a non changing time stamp whenever a row is added using the button "New Sample" in the below excel sheet

sample drop off.JPG

I think the problem is the function =IF(B3<>"",IF(A3="",NOW(),A3),"") as the =now() function works the way I want but the dates/time change each time the document is updated.

I have set up the excel sheet to do enable iterative calculations... do I need to do that to VBA?
Is there a better way to get the results I want than the above method?

Thanks

**MrShorty** · 09-28-2021, 10:26 PM

Part of the reason for using that kind of formula is so you can get non-changing timestamps into a spreadsheet without using VBA. In this case, VBA is doing the thing that you want timestamped, so have VBA enter the time stamp.

'code to insert and format new row
Sheets("Sheet1").Range("A4").Select
ActiveCell.Value=Now()
or
Sheets("Sheet1").Range("A4").Select
ACtiveCell.Value=CDbl(Now()) 'in case Excel has any trouble converting the date data type output of the Now() function to its native double data type