Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating VBA cell reference
I have a VBA macro in an Excel spreadsheet which includes a cell reference
say AX49. Of course, when I insert new columns/rows etc, the reference does not update. Is there any way I can write the reference in the macro so that it will automatically update? TIA V |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating VBA cell reference
On Sun, 25 Nov 2012 09:45:03 -0000, Victor Delta wrote:
I have a VBA macro in an Excel spreadsheet which includes a cell reference say AX49. Of course, when I insert new columns/rows etc, the reference does not update. Is there any way I can write the reference in the macro so that it will automatically update? Don't use a cell reference; use a defined name. Defined names do automatically update when you insert or remove rows or columns. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com Shikata ga nai... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating VBA cell reference
After serious thinking Victor Delta wrote :
I have a VBA macro in an Excel spreadsheet which includes a cell reference say AX49. Of course, when I insert new columns/rows etc, the reference does not update. Is there any way I can write the reference in the macro so that it will automatically update? TIA V I agree with Stan's suggestion so long as the defined name is meaningful so that anyone reading the formula can understand what it's referring to or doing. For example, if you have a col labeled/named "Rate", another col labeled/named "Amount", and another labeled (and optionally named) "Total". In the Total column you could enter the following formula in all cells as needed... =Amount*Rate ...where anyone reading this formula --should-- immediately understand what it's doing without having to trace where it refs to find this out. Note that both the refs used here are defined as 'Column-Absolute, Row-Relative' as described below. <FYI The reason your existing formula does not 'appear' to update is because the cell ref is fully relative. That means it updates relative to the cell where it's used, *NOT* where it refs. You must use absolute refs to cells if you don't want refs to shift when adding/deleting rows/cols on your worksheets. For example... Fully Absolute: "$AX$49" This will always ref that cell 'absolutely'. If you insert a col to the left this will update to "$AY$49" because the use/ref has shifted 1 col to the right. If you insert a row above row49 this will update to "$AX$50" because the use/ref has shifted down 1 row. Column-Absolute, Row-Relative: "$AX49" This will always ref col "AX" absolutely, in any row where the formula is used. If you insert a col to the left this will update to "$AY49" because the use/ref has shifted 1 col to the right. If you insert a row above row49 this will update to "$AX50" because the use only has shifted down 1 row. Row-Absolute, Column-Relative: "AX$49" This will always ref row 49 of whatever col using the formula is. If you insert a row above row49 this will update to "AX$50" because the use/ref has shifted down 1 row. If you insert a col to the left this will update to "AY$49" because the use only has shifted 1 col to the right. Obviously, deleting a row above or col left of the refs will have the same effect in the opposite direction. <FWIW It's also considered 'good practice' by some that defined names are given local (sheet-level) scope, and only given global (workbook-level) scope when absolutely necessary. Following this practice obviates name conflicts (with global scope names) when copying sheets to other workbooks. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating VBA cell reference
"Stan Brown" wrote in message
t... On Sun, 25 Nov 2012 09:45:03 -0000, Victor Delta wrote: I have a VBA macro in an Excel spreadsheet which includes a cell reference say AX49. Of course, when I insert new columns/rows etc, the reference does not update. Is there any way I can write the reference in the macro so that it will automatically update? Don't use a cell reference; use a defined name. Defined names do automatically update when you insert or remove rows or columns. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com Shikata ga nai... Very many thanks - never thought of that although I somehow knew there'd be a simple answer! V |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3-d reference is not updating | Excel Discussion (Misc queries) | |||
Updating a cell reference | Excel Discussion (Misc queries) | |||
Cell Reference updating | Charts and Charting in Excel | |||
Updating a reference ROW | Excel Discussion (Misc queries) | |||
Updating Indirect Reference(s) | Excel Worksheet Functions |