Macro Reference Cells

[Guest]

Problem: When I write a macro that acts on certain cells, the macro fails to
act properly if I insert rows above those cells that are normally acted upon.

I've tried to use both relative and absolute references, but when the row is
inserted the original cell reference in Visual Basic is not changed, but
remains the same.

Question: Is there a way to write a macro so that when row or column is
inserted the macro can go from any cell and find the correct cells to operate
for the macro?

 

[Guest]

The short answer is no - there is no automatic adjustment of addresses used
in code.

That said, most work around the column by analyzing the structure of the
worksheet to identify the area they need to work. This would mean looking
for unique attributes and values associated with the cells. Another
approach is to use a defined name (insert =>Name=>Define) with refers to the
upper left corner of the range (as an example). This would be adjusted by
excel as rows are inserted and VBA could rely on that.

 

[Guest]

Use named ranges instead. For example say cell A7 is called _A7 and contains
a vlaue of XXXXX and you insert a row above if you refer to the named range
it will now be A8 that now contains XXXXX.

Mike