define variable range
 

Hello -

I'm modifying some code from Dave McRitchie to create a TOC and need
some help ...

I've set up an InputBox where users select a cell, which should become
the upper-left corner of the TOC -- this cell is called 'StartCell'.
The code goes on to establish the range (rg), which is seven columns
by the number of sheets in the book. For example:
StartCell = A2
ActiveWorkbook.Sheets.Count = 10
RESULT should be that rg = A2:G12

Here's the code with my latest attempt to make it happen:
Set rg = Range(StartCell, Cells(ActiveWorkbook.Sheets.Count,
StartCell.Offset(0, 7)))

what am I doing wrong?

tia, ray

define variable range
 

Set rg = StartCell.Resize(ActiveWorkbook.Sheets.Count,7)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Ray" wrote in message
ups.com...
Hello -

I'm modifying some code from Dave McRitchie to create a TOC and need
some help ...

I've set up an InputBox where users select a cell, which should become
the upper-left corner of the TOC -- this cell is called 'StartCell'.
The code goes on to establish the range (rg), which is seven columns
by the number of sheets in the book. For example:
StartCell = A2
ActiveWorkbook.Sheets.Count = 10
RESULT should be that rg = A2:G12

Here's the code with my latest attempt to make it happen:
Set rg = Range(StartCell, Cells(ActiveWorkbook.Sheets.Count,
StartCell.Offset(0, 7)))

what am I doing wrong?

tia, ray

define variable range
 

Well THAT was easy ... guess I shoulda known that!

thanks Bob!