Worksheet Cell Range Name within VB Subroutine

[Jay Somerset]

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.

 

[Guest]

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.

 

[Guest]

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

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

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.

 

[Jim May]

How does one refer to a named area in a worksheet from within a VB macro
subroutine?
Having named B46 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
B46

 

[Witch-Doctor]

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.

 

[Witch-Doctor]

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.