How does one refer to a named area in a worksheet from within a VB macro
subroutine? I know how to refer to a selection, but not a block of cells
that has been named using the Insert|Name|Define dialog.

Example: I have a 3x3 block of cells named AA. How can I refer to the
individual cells within AA [e.g. AA(1,2)] within the VB coding of a macro
(subroutine)? How can I make the name AA visible within the subroutine, and
define it a s 3x3 array?

Thanks.
--
Jay.
(remove dashes for legal email address)

A named area is just a range with a name associated with it:

Sub name_it()
Dim n As Name
Dim r As Range
Dim s As String

For Each n In ThisWorkbook.Names
MsgBox (n.Name)
s = Range(n).Address
MsgBox (s)
Next n

End Sub

n behaves like a string. You can loop until you have the right name and then
SET r=Range(n) and use r like any other range, use all the usual methods
like Clear, use Offsets, Select, etc.
--
Gary's Student


"Jay Somerset" wrote:

How does one refer to a named area in a worksheet from within a VB macro
subroutine? I know how to refer to a selection, but not a block of cells
that has been named using the Insert|Name|Define dialog.

Example: I have a 3x3 block of cells named AA. How can I refer to the
individual cells within AA [e.g. AA(1,2)] within the VB coding of a macro
(subroutine)? How can I make the name AA visible within the subroutine, and
define it a s 3x3 array?

Thanks.
--
Jay.
(remove dashes for legal email address)

if its named AA the vb name is range("AA")
--
paul
remove nospam for email addy!



"Gary''s Student" wrote:

A named area is just a range with a name associated with it:

Sub name_it()
Dim n As Name
Dim r As Range
Dim s As String

For Each n In ThisWorkbook.Names
MsgBox (n.Name)
s = Range(n).Address
MsgBox (s)
Next n

End Sub

n behaves like a string. You can loop until you have the right name and then
SET r=Range(n) and use r like any other range, use all the usual methods
like Clear, use Offsets, Select, etc.
--
Gary's Student


"Jay Somerset" wrote:

How does one refer to a named area in a worksheet from within a VB macro
subroutine? I know how to refer to a selection, but not a block of cells
that has been named using the Insert|Name|Define dialog.

Example: I have a 3x3 block of cells named AA. How can I refer to the
individual cells within AA [e.g. AA(1,2)] within the VB coding of a macro
(subroutine)? How can I make the name AA visible within the subroutine, and
define it a s 3x3 array?

Thanks.
--
Jay.
(remove dashes for legal email address)

How does one refer to a named area in a worksheet from within a VB macro
subroutine?
Having named B4:D6 MyNamedRange

Range("MyNamedRange").select

How can I refer to the individual cells within AA [e.g. AA(1,2)] within the
VB coding of a macro (subroutine)?

Set MyRng = Range("MyNamedRange")
MyRng(7).select <<< would select the row 3 column 1 of the 3 X 3 block
B4:D6



"Jay Somerset" wrote in message
...
How does one refer to a named area in a worksheet from within a VB macro
subroutine? I know how to refer to a selection, but not a block of cells
that has been named using the Insert|Name|Define dialog.

Example: I have a 3x3 block of cells named AA. How can I refer to the
individual cells within AA [e.g. AA(1,2)] within the VB coding of a macro
(subroutine)? How can I make the name AA visible within the subroutine,
and
define it a s 3x3 array?

Thanks.
--
Jay.
(remove dashes for legal email address)

On Sat, 04 Feb 2006 19:33:22 -0500, Jay Somerset
wrote:

How does one refer to a named area in a worksheet from within a VB macro
subroutine? I know how to refer to a selection, but not a block of cells
that has been named using the Insert|Name|Define dialog.

Example: I have a 3x3 block of cells named AA. How can I refer to the
individual cells within AA [e.g. AA(1,2)] within the VB coding of a macro
(subroutine)? How can I make the name AA visible within the subroutine, and
define it a s 3x3 array?

Thanks.

The worksheet names are available within a VB macro. Try using...
Range("name").Cells(i, j).Value to refer to the (i,j) member of an
array (cell range) to which the name "name" has been assigned on the
worksheet.

On Sat, 04 Feb 2006 19:33:22 -0500, Jay Somerset
wrote:

How does one refer to a named area in a worksheet from within a VB macro
subroutine? I know how to refer to a selection, but not a block of cells
that has been named using the Insert|Name|Define dialog.

Example: I have a 3x3 block of cells named AA. How can I refer to the
individual cells within AA [e.g. AA(1,2)] within the VB coding of a macro
(subroutine)? How can I make the name AA visible within the subroutine, and
define it a s 3x3 array?

Thanks.

The worksheet names are available within a VB macro. Try using...
Range("name").Cells(i, j).Value to refer to the (i,j) member of an
array (cell range) to which the name "name" has been assigned on the
worksheet.


--
Jay.
(remove dashes for legal email address)