Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want chart source data to be relative references, not absolute. | Charts and Charting in Excel | |||
Named ranges: don't want absolute references | Excel Discussion (Misc queries) | |||
Macro Help In Excel | Excel Discussion (Misc queries) | |||
Absolute References in cell formula | Excel Worksheet Functions | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) |