Macro to Insert Line & Transfer Formula Not Updating Cell References Correctly

[Big.Moe]

Hello Everyone,

A while ago, I created a button in a spreadsheet so that if the user needed to add additional lines, they could just click the button and a VBA code would insert a new row and transfer the formulas to new row.

Here is the code:

Please Login or Register  to view this content.

That has been working perfectly for a couple years, but recently I had to add a column (Col J) in file below that tabulates a running daily total.

This is the formula in those cells:

Please Login or Register  to view this content.

Bait Sheet.jpg

A simple formula that works fine, except when a line is inserted. When you insert a line with the VBA button some of the cell references in the cell immediately below are updated and some are not.

Here is an example:
- Original Formula in cell J795: =IF(A795=A794,J794+F795,F795)
- Line inserted just above pushes cell J795 down and makes it cell J796
- Formula after line inserted: =IF(A796=A794,J794+F796,F796)

As you can see, the 795 references were updated to 796, but the 794 references stayed the same, which messes up the calculations. This only occurs in the line immediately following the inserted line. The remainder of the cells below are updated correctly.

Is there anyway to rectify this?

Thank you for your time.

[Big.Moe]

I did some more research on this issue and found that the problem was not the VBA macro itself, but rather an issue inherent with Excel itself.

Here is a Microsoft Office forum post that deals with this issue:
https://answers.microsoft.com/en-us/...7-064c5b6b4c2f

They offer a clever work around by substituting previous line references with "INDIRECT("A"&ROW()-1)"

Seems to do the trick.