Selecting a variable range
 

Hi Group,
I'm trying to select a range using VBA which is dependent on the values held
in two named ranges within the main workbook.
These named ranges are "Rows" and "Columns". The idea is that starting from
a defined startpoint (Cell C4), the VBA allows a selection from C4 to, for
example E6. To allow me to do this, I've tried using the R1C1 format and
come up with the following...
ActiveWorkbook.Range(RefersToR1C1:="R4C3" & ":R" & "rows" & "C" &
"Columns").Select
As I guess you realise due to my posting here, it doesn't work. In the above
example, the value in "Rows" would be 6 and "Columns" would be 5 (to give
E6).

Any suggestions would be welcome.
Regards
Colin Foster

Selecting a variable range
 

Hi
try something like

sub foo()
dim row_no as long
dim col_no as integer
dim rng as range

with activesheet
row_no=.range("A1").value
col_no = .range("B1").value
set rng = range(cells(4,3),cells(row_no,col_no))
msgbox rng.address
end with
end sub

--
Regards
Frank Kabel
Frankfurt, Germany

"Colin Foster" schrieb im Newsbeitrag
...
Hi Group,
I'm trying to select a range using VBA which is dependent on the
values held
in two named ranges within the main workbook.
These named ranges are "Rows" and "Columns". The idea is that
starting from
a defined startpoint (Cell C4), the VBA allows a selection from C4
to, for
example E6. To allow me to do this, I've tried using the R1C1 format
and
come up with the following...
ActiveWorkbook.Range(RefersToR1C1:="R4C3" & ":R" & "rows" & "C"
&
"Columns").Select
As I guess you realise due to my posting here, it doesn't work. In
the above
example, the value in "Rows" would be 6 and "Columns" would be 5 (to
give
E6).

Any suggestions would be welcome.
Regards
Colin Foster

Selecting a variable range
 

You are ending up with the string "R4C3:RrowsCcolumns". Not what you want. You
haven't told VBA that Rows and Columns are the names of ranges. As written,
they are just literal text. You would need to write

":R" & Range("Rows").Value & "C" & Range("Columns").Value

But there are additional problems.

1. re RefersToR1C1: this is a property of a Name object, not of a workbook.
From Help: "Returns or sets the formula that the name refers to."

2. Workbooks don't have Range properties; Worksheets do.

Anyway, I would write it this way. A bit more verbose, but I think easier to
read:

With ActiveWorkbook.ActiveSheet
R = .Range("Rows").Value
C = .Range("Columns").Value
.Range("C4", .Cells(R, C)).Select
End With

You may not need to select anything at all. What does your code do next?


On Tue, 26 Oct 2004 21:07:16 +0100, "Colin Foster"
wrote:

Hi Group,
I'm trying to select a range using VBA which is dependent on the values held
in two named ranges within the main workbook.
These named ranges are "Rows" and "Columns". The idea is that starting from
a defined startpoint (Cell C4), the VBA allows a selection from C4 to, for
example E6. To allow me to do this, I've tried using the R1C1 format and
come up with the following...
ActiveWorkbook.Range(RefersToR1C1:="R4C3" & ":R" & "rows" & "C" &
"Columns").Select
As I guess you realise due to my posting here, it doesn't work. In the above
example, the value in "Rows" would be 6 and "Columns" would be 5 (to give
E6).

Any suggestions would be welcome.
Regards
Colin Foster

Selecting a variable range
 

Sorted!!!
Basically, once the range is created/selected, then a format is applied to
it. Basically this will save loads of time in creating lots of grids!
Thanks Myrna (and Frank) for your suggestions
Regards
Colin Foster
"Myrna Larson" wrote in message
...
You are ending up with the string "R4C3:RrowsCcolumns". Not what you want.
You
haven't told VBA that Rows and Columns are the names of ranges. As
written,
they are just literal text. You would need to write

":R" & Range("Rows").Value & "C" & Range("Columns").Value

But there are additional problems.

1. re RefersToR1C1: this is a property of a Name object, not of a
workbook.
From Help: "Returns or sets the formula that the name refers to."

2. Workbooks don't have Range properties; Worksheets do.

Anyway, I would write it this way. A bit more verbose, but I think easier
to
read:

With ActiveWorkbook.ActiveSheet
R = .Range("Rows").Value
C = .Range("Columns").Value
.Range("C4", .Cells(R, C)).Select
End With

You may not need to select anything at all. What does your code do next?


On Tue, 26 Oct 2004 21:07:16 +0100, "Colin Foster"

wrote:

Hi Group,
I'm trying to select a range using VBA which is dependent on the values
held
in two named ranges within the main workbook.
These named ranges are "Rows" and "Columns". The idea is that starting
from
a defined startpoint (Cell C4), the VBA allows a selection from C4 to,
for
example E6. To allow me to do this, I've tried using the R1C1 format and
come up with the following...
ActiveWorkbook.Range(RefersToR1C1:="R4C3" & ":R" & "rows" & "C" &
"Columns").Select
As I guess you realise due to my posting here, it doesn't work. In the
above
example, the value in "Rows" would be 6 and "Columns" would be 5 (to give
E6).

Any suggestions would be welcome.
Regards
Colin Foster