VBA Causing Formula In able to Not Copy Down to New Rows

[KAELondon]

Hello,

I the below sub in order to enter timestamps into a table; the last column of the table works out the duration between each timestamp but, for some reason, this script stops the formula in the last column from copying down as new rows are added to the table. It's the most bizare thing. That column isn't even unlocked for editing and the ranges don't crossover into that column at all. Has anyone experienced this before? Any ideas how to fix?

The target range is the "NAME" column and my script posts the timestamp into the "TIME ON" column next to the name entered and also the "TIME OFF" column in the row above with the "DUR." column being the duration. I've tested this out and the formula remains fine with no VBA on the sheet but stops working I enter this sub:

-------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'The ranges that we are checking is where the names are
If Not Intersect(Target, Range("B10:B100")) Is Nothing Then

On Error Resume Next

'If the value is empty
If Target.Value = "" Then

'Make sure that the relevant cells are empty
Target.Offset(0, 3) = ""
Target.Offset(-1, 4) = ""

Else
'Otherwise put the timestamp in the "TIME ON" & "TIME OFF" cols
Target.Offset(0, 3).Value = Format(Now, "mm/dd/yy HH:mm")
Target.Offset(-1, 4).Value = Format(Now, "mm/dd/yy HH:mm")

End If

End If

End Sub
----------------------------------------------------------------------------------

Cell "B10" is where the "NAME" column starts.

 

[Joe4]

Are you saying there is an issue when you go to insert rows on your worksheet?

If so, then try adding this line (in red) near the top of your code:

If Target.CountLarge > 1 Then Exit Sub

'The ranges that we are checking is where the names are
If Not Intersect(Target, Range("B10:B100")) Is Nothing Then

I would also recommend removing this line from your code:

On Error Resume Next

This could be suppressing important error message that clue you in on what is going on!

 

[KAELondon]

No, there are no issues adding rows; It's just that any new rows added with the sub in VBA present will be empty and not contain the formular above.

The "DUR." column has a formula which subtracts start time from end time to give me a duration (=IF(ISBLANK([@END]),"",[@END]-[@START])); that works all good. If I were to extend the table down all new rows in the "DUR." column will contain the formula (as they should). However, when I add the sub in VBa to give me the timestamps all rows added after that will be blank. I have no idea why.

 

[KAELondon]

I should add, just the sub being present is enough for the formula to not copy down; the code doesn't actually have to be doing anything as part of its function. Furthermore, any existing rows that had the formula retain it. So... If everything is blank in the VBA then the formula copies down no problem but the moment I past the sub into the VBA all new rows will be blank and not contain the formula (all rows created before pasting in the VBA will still have the formula though).

 

[Joe4]

No, there are no issues adding rows; It's just that any new rows added with the sub in VBA present will be empty and not contain the formular above.

Exactly what columns (column letters) do these formulas that you want copied down reside in?

 

[KAELondon]

I've got (=IF(ISBLANK([@END]),"",[@END]-[@START])) in Column G (So 5 columns over from the range of the sub and the sub puts the timestamp in column E and in the row above in Column F.

 

[Joe4]

How/where exactly are you adding new rows to this table?
Are you inserting in the middle of the table, or just trying to enter a new row under the last row in the table?

 

[KAELondon]

The latter. I have a module doing it for me...

ActiveSheet.ListObjects("Table7").ListRows.Add

But I've tried doing it manually and it's the same result. If the sub is present newly added rows are blank.

 

[Joe4]

The latter. I have a module doing it for me...

ActiveSheet.ListObjects("Table7").ListRows.Add

But I've tried doing it manually and it's the same result. If the sub is present newly added rows are blank.

So, you have other VBA code interacting with this workbook also?
Seems to be a number of moving parts.

It might be a lot easier to try to figure out what is going on if we had access to the workbook, so we can see all the different pieces interacting at play here.
Any chance you can remove any sensitive data, and upload a copy of it to a file sharing site for us to download?
Also note that "ListRows.Add" has some optional parameters, and I see some comments in various Google searches talking about using the "AlwaysInsert" parameter:

ListRows.Add method (Excel)

Office VBA reference topic

learn.microsoft.com