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

Use named range instead of literal

Cheers
--
AP

"Atreides" <atreides1AThotmailD0Tcom> a écrit dans le message de news:
713AE028-8CC1-455C-872A-(E-Mail Removed)...
> 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

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" wrote:

> Use named range instead of literal
>
> Cheers
> --
> AP
>
> "Atreides" <atreides1AThotmailD0Tcom> a écrit dans le message de news:
> 713AE028-8CC1-455C-872A-(E-Mail Removed)...
> > 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
>
>
>

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,
--
AP


"Atreides" <atreides1AThotmailD0Tcom> a écrit dans le message de news:
C36E5F06-C5E7-4662-9CEA-(E-Mail Removed)...
> 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" wrote:
>
>> Use named range instead of literal
>>
>> Cheers
>> --
>> AP
>>
>> "Atreides" <atreides1AThotmailD0Tcom> a écrit dans le message de news:
>> 713AE028-8CC1-455C-872A-(E-Mail Removed)...
>> > 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
>>
>>
>>

Thanks Ardus, that explains it well.

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

Atreides

"Ardus Petus" wrote:

> 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,
> --
> AP
>
>
> "Atreides" <atreides1AThotmailD0Tcom> a écrit dans le message de news:
> C36E5F06-C5E7-4662-9CEA-(E-Mail Removed)...
> > 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" wrote:
> >
> >> Use named range instead of literal
> >>
> >> Cheers
> >> --
> >> AP
> >>
> >> "Atreides" <atreides1AThotmailD0Tcom> a écrit dans le message de news:
> >> 713AE028-8CC1-455C-872A-(E-Mail Removed)...
> >> > 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
> >>
> >>
> >>
>
>
>