Help with absolute references in a macro
 

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?

Help with absolute references in a macro
 

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" wrote in message
...
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?

Help with absolute references in a macro
 

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
...
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" wrote in message
...
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?

Help with absolute references in a macro
 

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
...
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" wrote in message
...
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?

Help with absolute references in a macro
 

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" wrote in message
...
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
...
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" wrote in message
...
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?

Help with absolute references in a macro
 

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" wrote in message
...
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
...
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" wrote in message
...
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?