Help with absolute references in a macro

Manty · 01-12-2006, 02:25 PM

By selecting an option button, I would like macro to enter text into a
particular cell. However, I would like the cell to remain fixed, even if
rows/colums are inserted. Right now, the macro is refering to cell C1, and I
would like it to refer to cell $C$1. Is this possible?

Bernie Deitrick · 01-12-2006, 02:35 PM

Manty,

Macros are static, and aren't affected by row/column insertions unless you use named ranges, so
you're good to go.

HTH,
Bernie
MS Excel MVP

"Manty" <[email protected]> wrote in message
news:[email protected]...
> By selecting an option button, I would like macro to enter text into a
> particular cell. However, I would like the cell to remain fixed, even if
> rows/colums are inserted. Right now, the macro is refering to cell C1, and I
> would like it to refer to cell $C$1. Is this possible?

Bernie Deitrick · 01-12-2006, 02:50 PM

I really should be more specific in my wording:

Range addresses typed into VBA code are static, so

Range("C1").Value = ....

will always point to the current cell C1, no matter what (often to the dismay of the user). There
are many, many ways of writing static code that use ranges in dynamic ways.

HTH,
Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:%[email protected]...
> Manty,
>
> Macros are static, and aren't affected by row/column insertions unless you use named ranges, so
> you're good to go.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Manty" <[email protected]> wrote in message
> news:[email protected]...
>> By selecting an option button, I would like macro to enter text into a
>> particular cell. However, I would like the cell to remain fixed, even if
>> rows/colums are inserted. Right now, the macro is refering to cell C1, and I
>> would like it to refer to cell $C$1. Is this possible?
>
>

Manty · 01-13-2006, 05:35 AM

Thanks Bernie,

I think I need to rephrase my question:

Right now the Macro inserts text into cell C1. If I were to insert a row
above that cell, the cell that I was originally inserting the text into would
now be D1 - I need the marco to "follow" the original cell, nomatter where it
goes.

Is this possible?

Rob

"Bernie Deitrick" wrote:

> I really should be more specific in my wording:
>
> Range addresses typed into VBA code are static, so
>
> Range("C1").Value = ....
>
> will always point to the current cell C1, no matter what (often to the dismay of the user). There
> are many, many ways of writing static code that use ranges in dynamic ways.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:%[email protected]...
> > Manty,
> >
> > Macros are static, and aren't affected by row/column insertions unless you use named ranges, so
> > you're good to go.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Manty" <[email protected]> wrote in message
> > news:[email protected]...
> >> By selecting an option button, I would like macro to enter text into a
> >> particular cell. However, I would like the cell to remain fixed, even if
> >> rows/colums are inserted. Right now, the macro is refering to cell C1, and I
> >> would like it to refer to cell $C$1. Is this possible?
> >
> >
>
>
>

Bernie Deitrick · 01-13-2006, 09:35 AM

Manty,

Sorry for mis-understanding your question.

Yes. Name cell C1 using Insert / Name / Define. Call it, for example "myTextCell"

Then in your code, where you have

Range("C1").Value.... (or any other reference to Range("C1"))

use

Range("myTextCell").Value....

Note, however, that if Cell C1 gets deleted, then this will fail.

HTH,
Bernie
MS Excel MVP

"Manty" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bernie,
>
> I think I need to rephrase my question:
>
> Right now the Macro inserts text into cell C1. If I were to insert a row
> above that cell, the cell that I was originally inserting the text into would
> now be D1 - I need the marco to "follow" the original cell, nomatter where it
> goes.
>
> Is this possible?
>
> Rob
>
> "Bernie Deitrick" wrote:
>
>> I really should be more specific in my wording:
>>
>> Range addresses typed into VBA code are static, so
>>
>> Range("C1").Value = ....
>>
>> will always point to the current cell C1, no matter what (often to the dismay of the user).
>> There
>> are many, many ways of writing static code that use ranges in dynamic ways.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
>> news:%[email protected]...
>> > Manty,
>> >
>> > Macros are static, and aren't affected by row/column insertions unless you use named ranges, so
>> > you're good to go.
>> >
>> > HTH,
>> > Bernie
>> > MS Excel MVP
>> >
>> >
>> > "Manty" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> By selecting an option button, I would like macro to enter text into a
>> >> particular cell. However, I would like the cell to remain fixed, even if
>> >> rows/colums are inserted. Right now, the macro is refering to cell C1, and I
>> >> would like it to refer to cell $C$1. Is this possible?
>> >
>> >
>>
>>
>>

Manty · 01-13-2006, 10:30 AM

Bernie,

Works perfectly, thank you very much for your help!

Manty

"Bernie Deitrick" wrote:

> Manty,
>
> Sorry for mis-understanding your question.
>
> Yes. Name cell C1 using Insert / Name / Define. Call it, for example "myTextCell"
>
> Then in your code, where you have
>
> Range("C1").Value.... (or any other reference to Range("C1"))
>
> use
>
> Range("myTextCell").Value....
>
> Note, however, that if Cell C1 gets deleted, then this will fail.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Manty" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Bernie,
> >
> > I think I need to rephrase my question:
> >
> > Right now the Macro inserts text into cell C1. If I were to insert a row
> > above that cell, the cell that I was originally inserting the text into would
> > now be D1 - I need the marco to "follow" the original cell, nomatter where it
> > goes.
> >
> > Is this possible?
> >
> > Rob
> >
> > "Bernie Deitrick" wrote:
> >
> >> I really should be more specific in my wording:
> >>
> >> Range addresses typed into VBA code are static, so
> >>
> >> Range("C1").Value = ....
> >>
> >> will always point to the current cell C1, no matter what (often to the dismay of the user).
> >> There
> >> are many, many ways of writing static code that use ranges in dynamic ways.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> >> news:%[email protected]...
> >> > Manty,
> >> >
> >> > Macros are static, and aren't affected by row/column insertions unless you use named ranges, so
> >> > you're good to go.
> >> >
> >> > HTH,
> >> > Bernie
> >> > MS Excel MVP
> >> >
> >> >
> >> > "Manty" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> By selecting an option button, I would like macro to enter text into a
> >> >> particular cell. However, I would like the cell to remain fixed, even if
> >> >> rows/colums are inserted. Right now, the macro is refering to cell C1, and I
> >> >> would like it to refer to cell $C$1. Is this possible?
> >> >
> >> >
> >>
> >>
> >>
>
>
>