If I select A1:A4 and drag it to G, range stays A1:A5.
If I select A1:A5 (or more, say A1:A8) and drag to G the new
reference is G1:G5 and the code looks for MyString where ever it is
Well.., you should be explicit when stating range refs. In your
example, the original ref was...
=$A$1:$A$5
...and the new ref after moving is...
=$G$1:$G$5
...where this is a *fully absolute* ref. What you posted was *fully
relative* refs. What determines this is the *$* symbol and where it is
positioned with respect to the col/row labels in the range address.
Also, you didn't state that you *explicitly* forced local (sheet level)
scope by including the sheetname when you typed the range name in the
Namebox. The result is the defined named has global (workbook level)
scope which you would *only use when absolutely necessary* if you
follow using 'best practices' in your work!
There are 2 more ref types...
ColAbsolute/RowRelative: e.g.: $A1:$A5
ColRelative/RowAbsolute: e.g.: A$1:A$5
...both of which present specific behavior *relative* to the
non-absolute portion of the ref, and *absolute* to the non-relative
portion.
<Usage example>
Say you have a table of amounts on Sheet1 that you need a simple SUM
formula at the bottom. This table has a headings row.
Select A2
Defined name: 'Sheet1'!Hdr_Row
ReferTo: =A$1
Description: Creates a colRelative/rowAbsolute ref *relative* to the
same col as the currently active cell.
Defined name: 'Sheet1'!FirstCell
ReferTo: =OFFSET(Hdr_Row,1,0)
Description: Creates a colRelative/rowAbsolute ref *relative* to the
same col as the currently active cell, absolute to the row below
the
headings row.
Defined name: 'Sheet1'!LastCell
ReferTo: =A1
Description: Creates a fully relative ref *relative* to the
same col as the currently active cell and the row before the
currently active cell.
So your table has say 10 rows of amounts and the totals go in row 12 so
that the table area is contiguous headings to totals. Select all the
cells to receive totals in the totals row and type the following
formula...
=SUM(FirstRow:LastRow)
...then while holding down the Ctrl key, press Enter to put the formula
into all selected cells in one shot.
Now you can insert/delete rows anywhere between the headings row and
the totals row without breaking anything.
Note that the formula reads the same in each totals cell, and it's
self-explanatory as to what cells are involved. IMO, this makes for
easier understanding of how the formula works compared to using cell
addresses that have to be followed in order for the user to figure it
out!
Note that all the named ranges have local scope, allowing the same
names to be used on other sheets with similar requirements.
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
