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

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...

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

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