W
willhandley
Excel 2000 (SP-3) running on Win 2000 Professional (SP 4)
This sounds like a Microsoft internal error, but I'm posting just in
case it's something I'm doing wrong and can correct. I apologize if
this is not new, but I searched diligently and didn't find anything,
but it is kind of hard to search for.
I have a sheet with a row in which each of 98 cells contains a counting
formula for a value in the cells above it. Rows above it are inserted
programmatically. Each row inserted is within the existing row range.
The formulas in every one of the cells to the left and right of one
particular cell are adjusted correctly by Excel after a row is
inserted, but the formula in this one single cell does not change. The
format of the formula in the "corrupt" cell is identical to the others;
the VBA code does not reference this cell at all; and the sheet is
protected, so neither my code nor a user could have corrupted this
manually (This is a commercial application with about a hundred users.
A user sent me this corrupt workbook).
The formula in adjacent cell to the left: =COUNTIF(BL$14:BL$122,$A125
The formula in adjacent cell to the right: =COUNTIF(BN$14:BN$122,$A125)
The corrupt formula: =COUNTIF(BM$14:BM$23,$A125)
The problem is that the corrupt formula should reference rows 14:122 as
well.
An interesting fact is that if I change the corrupt formula to be
correct, i.e., to reference the same row range as the adjacent cells,
after that every time I insert a new row the corrupt cell forumal does
get adjusted by Excel. But if I just change it to an arbirary row
range, just for the sake of making a change, e.g., it is still not
referencing the same row range as the adjacent cells, it does not
adjust when a row is inserted.
The formula in every cell started out referencing the range Row 14:19.
The formula was plugged into the first cell in the row programatically,
then copied to each adjacent cell:
indSched.Range(Cells(indx, 3), Cells(indx,
lastInterval)).Select
Selection.FillRight
Since the first (seed) cell's formula is still correct, the corrupt
cell's formula should also be correct.
The corrupt formula evidently worked for 4 row insertions, then after
that stopped working, while the rest continued to adjust after each
insertion (and still continue to change). Of course, it's also possible
all formulas were adjusted correctly until the 100th row, or any
arbitrary number, was inserted, then for some reason the formula in
this cell got clobbered with a bad row number. After that, as shown
above, the formula would no longer work as additional rows were
inserted.
In the interest of simplicity I omitted one fact in the above
description, tho I'm not sure it's relevant. Instead of a single
"counting" row with this problem, I actually have six adjacent rows,
each counting a different value in the rows above, but otherwise the
same formula. In that particular column (BM), the formula in every cell
stopped changing after row 23 was inserted and behaves the same way.
I'm keenly interested not so much in fixing this particular customer's
workbook, when I can obviously do manually, but in preventing this in
the future. This is the second customer to report this problem and it's
beginning to cast doubt on the dependability of the application.
I hate to think I have to create VBA code to make these formula changes
manually, thus duplicating functionality Excel already provides, but at
this point that begins to look like the only option.
This sounds like a Microsoft internal error, but I'm posting just in
case it's something I'm doing wrong and can correct. I apologize if
this is not new, but I searched diligently and didn't find anything,
but it is kind of hard to search for.
I have a sheet with a row in which each of 98 cells contains a counting
formula for a value in the cells above it. Rows above it are inserted
programmatically. Each row inserted is within the existing row range.
The formulas in every one of the cells to the left and right of one
particular cell are adjusted correctly by Excel after a row is
inserted, but the formula in this one single cell does not change. The
format of the formula in the "corrupt" cell is identical to the others;
the VBA code does not reference this cell at all; and the sheet is
protected, so neither my code nor a user could have corrupted this
manually (This is a commercial application with about a hundred users.
A user sent me this corrupt workbook).
The formula in adjacent cell to the left: =COUNTIF(BL$14:BL$122,$A125
The formula in adjacent cell to the right: =COUNTIF(BN$14:BN$122,$A125)
The corrupt formula: =COUNTIF(BM$14:BM$23,$A125)
The problem is that the corrupt formula should reference rows 14:122 as
well.
An interesting fact is that if I change the corrupt formula to be
correct, i.e., to reference the same row range as the adjacent cells,
after that every time I insert a new row the corrupt cell forumal does
get adjusted by Excel. But if I just change it to an arbirary row
range, just for the sake of making a change, e.g., it is still not
referencing the same row range as the adjacent cells, it does not
adjust when a row is inserted.
The formula in every cell started out referencing the range Row 14:19.
The formula was plugged into the first cell in the row programatically,
then copied to each adjacent cell:
indSched.Range(Cells(indx, 3), Cells(indx,
lastInterval)).Select
Selection.FillRight
Since the first (seed) cell's formula is still correct, the corrupt
cell's formula should also be correct.
The corrupt formula evidently worked for 4 row insertions, then after
that stopped working, while the rest continued to adjust after each
insertion (and still continue to change). Of course, it's also possible
all formulas were adjusted correctly until the 100th row, or any
arbitrary number, was inserted, then for some reason the formula in
this cell got clobbered with a bad row number. After that, as shown
above, the formula would no longer work as additional rows were
inserted.
In the interest of simplicity I omitted one fact in the above
description, tho I'm not sure it's relevant. Instead of a single
"counting" row with this problem, I actually have six adjacent rows,
each counting a different value in the rows above, but otherwise the
same formula. In that particular column (BM), the formula in every cell
stopped changing after row 23 was inserted and behaves the same way.
I'm keenly interested not so much in fixing this particular customer's
workbook, when I can obviously do manually, but in preventing this in
the future. This is the second customer to report this problem and it's
beginning to cast doubt on the dependability of the application.
I hate to think I have to create VBA code to make these formula changes
manually, thus duplicating functionality Excel already provides, but at
this point that begins to look like the only option.