[RESOLVED] Updating cells' references

[krtxmrtz]

This is a similar question to that I wrote in this thread but whereas I referred in it to a cell's contents, this now has to do with the cell's location.

If in a macro you have a line referencing to a cell, for example:

Cells(19, 5).Value = "100"

and then you insert a row above that cell, now the macro will place he value "100" in cell(20,5) instead. Is there an alternative way to write the macro so that you don't need to change the cell's col/row references in these cases? It's a pain in the neck and a source of errors when you have a large amount of references to manually update.

[DKenny]

s there an alternative way to write the macro so that you don't need to change the cell's col/row references in these cases?

The best alternative is to used "Named ranges".

[krtxmrtz]

The best alternative is to used "Named ranges".

I have no idea what these are, though the name sounds vaguely familiar. I've search with Google but despite a large number of hits they don't seem to bear a direct relationship with Excel. Could you please give me a hint?

[DKenny]

Have a look at "Define named cell references or ranges" in the Excel Help File.

[krtxmrtz]

Now, wait a minute. I think I typed "namespaces" without a blank separation. I've just tried "named spaces" and found this which seems more like it. Is that what you meant?

[krtxmrtz]

Have a look at "Define named cell references or ranges" in the Excel Help File.

I didn't notice you had already answered. Thanks.

[krtxmrtz]

Have a look at "Define named cell references or ranges" in the Excel Help File.

Thanks a lot, I think this made my day no matter how simple it may be.