Formula problem when inserting new rows...

[Tonibee]

Hi all, first post. I'm an occasional excel user... Usually I can figure things out but this seemingly innocuous problem has me stumped and I can't track down a solution.

I've written an accounting spreadsheet for myself. Column F is for adding bank transactions and column J tracks the bank balance by taking the entry from the previous row (e.g. J13) and adding the entry from column F of the current row (e.g. F14). The formula is =sum(J13+F14).

The formula copies fine in the usual way (using the drag handle). However, when I add a new row using the macro button, the cell reference for the J column freezes and doesn't advance, giving the formula =sum(J13+F15). I've tried two different macro codes to insert new rows and both have the same result. I've copied my macro below, but I think I'm missing something much more basic... Any ideas?

Thank you.

Private Sub CommandButton1_Click()Dim rw As Long, cl As Long, lastRw As Long
   
               lastRw = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Row
               ActiveSheet.Cells(lastRw, 1).Select
               Selection.EntireRow.Insert
               n = ActiveCell.Row - 1
               For cl = 1 To Columns.Count
                If Cells(n - 1, cl).HasFormula Then
                    Cells(n - 1, cl).Copy Cells(n + 1, cl)
                End If
               Next
                Cells(n, 1).Select
               
End Sub

 

[Special-K99]

FYI

You dont need a SUM() around that formula.

=sum(J13+F14)

is the same as

=J13+F14

 

[Tonibee]

Thank you, SpecialK-99

Although your answer isn't a solution, it's good enough to spark that annoying bit of realisation that makes one feel like a right numpty!! And it also gives me a bit of advice, so thanks!

Basically, I've populated the whole column at the outset with the formula by dragging the top cell to the bottom, and that is the problem! New rows are inserted between the lines with the existing formula and that's what messes it up. Deleting the formula from unused rows has fixed the problem!

Cheers!

FYI

You dont need a SUM() around that formula.

=sum(J13+F14)

is the same as

=J13+F14

 

[Special-K99]

Dont worry about it, I've seen this one twice today.