Formulas are skipping a row

[Carroll]

Hello,

I picked up some VBA that inserts a row, then copies the values down
from the row above to the new row, then keeps only those values in the
new row that contain formulas:

Sheets("IDA MI WrkGrp Rpt").Select
Range("A13").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate ' this finds the first cell
in the colum with
' nothing in it.
ActiveCell.Offset(-1).Activate - this moves me up one, to the last
cell with an entry.
ActiveCell.Offset(1).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(1).EntireRow
On Error Resume Next
ActiveCell.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
On Error GoTo 0

I modied it slightly to ignore errors coming from the ClearContents
when it finds no constants on that line.

Here is my problem. While it inserts the row, then keeps only cells
with formulas, not all of the formulas contain the values I expect.
For instance, where a cell copied down contains
=(SUM(B$14:B15)-SUM(D$1415)), the cell below it now has
=(SUM(B$14:B16)-SUM(D$1416)), which is correct. But for a cell that
contains =Receipt!B5, the cell below it has =Receipt!B7. Why did it
not contain =Receipt!B6? Why did it skip a number?

Very strange.

Thanks for reading.

Carroll Rinehart

 

[Kassie]

Hi Carroll

If you do not want the cell references to change, you have to make use of
fixed cell references. You click on the relevant cell, position your cursor
between the B and the 5, and press <F4>. It will now appear as
=Receipt!$B$5. You can also just type in the $ signs.

 

[Carroll]

Kassie,

I do want it to change. But I would have expected =Receipt!B5 to have
become =Receipt!B6 when the formula got copied down, not =Receipt!B7.
My question is, why did it skip a number?

Thanks,

Carroll Rinehart

 

[Kassie]

Hi Carroll

Reading problem on my side!

I do not understand why it does it on your side. I have set up a sheet with
similar formulae, and copied your code into a macro. It works fine on my
side, and does not skip a number.

 

[Tom Ogilvy]

I set up a worksheet as you described, copied your formulas from the email
and put them in the worksheet. I ran your macro and it worked as expected
It copied =receipt!B5 as =Receipt!B6 (Sum formula continued to return
expected results).

If I ran it a second time, I did notice unexpected (well, I expected it),
behavior. It inserted the new row before the row I had just inserted.
This is because there was no formula in columnA when it was copied down so
this cell became blank. Thus the second time it was run, it found the same
empty cell as before and made a premature insertion; so the original new row
remained the last row and the new new row was inserted before it. This may
not be your exact problem, but it could be a similar situation - that
insertions are not occuring where you think they will be.

In any event, the code in isolation should not cause the problem you
describe.

 

[Carroll]

Thanks you all for looking at this. While I have gotten it to work
just fine (in isolation), my "live" spreadsheet does not behave so
well. I have looked at this so many ways and have tried many minor
variations. In fact, I have a macro that inserts a line on every sheet
of my live spreadsheet, and some sheets in the spreadsheet have no
problem at all, while others fail to copy it down properly. I have yet
to isolate the problem.

Carroll

 

[Tom Ogilvy]

If you are inserting lines on all sheets, then the formula may be adjusting
automatically to reflect the new location of the cell it is refering to.

In otherwords, if I put in the formula

=Receipt!B6 in sheet1
then go to Receipt and insert a new row #2 (or any row prior to 6), then the
formula in Sheet1 will adjust to

=Receipt!B7

since you might be working with the last row in each sheet, this is a
distinct possibility.

 

[Carroll]

Tom,

That is just so logical, that I would bet money you're right. I'll go
back and check.

Carroll

 

[Carroll]

Yes. I was able to fix it by changing the order in which I added rows
to various sheets. I had to go back and find where all the dependencies
were. I broke it down into 3 groups: do those sheets with absolutely no
dependencies on other sheets first; then do those sheets with
dependencies on the first group, but also fed data to other sheets;
then finally added rows to those sheets that were entirely dependent on
getting their data from other sheets.

So thanks again Tom for setting me straight. I have been banging my
head for quite a few days now.

Carroll