How do I make references to cells in subroutines relative?

[Guest]

How do I make references to cells in subroutines relative?

For example, if part of my subroutine is:

Range("A533").Select

then I insert or delete cells in the spreadsheet, the references to these
cells in the macro stay the same, and are thus incorrect. They always have to
be updated manually. How can I make these change automatically when I modify
the spreadsheet?

Or is there a better way to reference cells in the worksheet?

Thanks
Atreides

 

[Ardus Petus]

Use named range instead of literal

Cheers

 

[Guest]

Hi Ardus,

Sorry, I'm not sure what you mean by that. I've leart VBA by experiment
rather than instruction, so I'm not really familiar with the terms. How do I
use a named range?

Thanks
Peter

 

[Ardus Petus]

Select the range you want to name
Use menu Insert>Name>Define
enter a valid name (eg: MyRange)
click OK

Now you can use:
Range("MyRange").Select
anywhere in your code.

Insertions and deletions will be reflected in your name definaition

Cheers,

 

[Guest]

Thanks Ardus, that explains it well.

It seems that Names are even more useful than I previously thought!

Atreides