Help in using an Offset in a .Range().Select

**roblo** · 08-25-2005, 04:03 PM

I am trying to Select a Range of cells where the last cell is calcualted and will be plugged in at run time.

worksheets.Range("A1:C1").Select ' This works of course, selecting A1 to C1 but I wanted the ending cell to be calculated.

worksheets.Range("A1", Range("A1").Offset(colTOright)).Select

Where colTOright will be calculated earlier. I tried just plugging in a number for colTOright but get an VBScript runtime error, Type mismatch: 'Range'

Is there another way?

Thanks
Robert

Jake Marx · 08-25-2005, 06:05 PM

Hi Robert,

A few problems with your code. First, you need to specify which worksheet
you're working with. Secondly, you should make sure your range references
are fully-qualified (easiest way is to use a With block). Here's some
modified code (untested) which should work:

With Worksheets("Sheet1")
.Range(.Range("A1"), .Range("A1").Offset(colTOright)).Select
End With

Even easier would be something like this:

Worksheets("Sheet1").Range("A1").Resize(ColumnSize:=colTOright +
1).Select

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

roblo wrote:
> I am trying to Select a Range of cells where the last cell is
> calcualted and will be plugged in at run time.
>
> worksheets.Range("A1:C1").Select ' This works of course, selecting
> A1 to C1 but I wanted the ending cell to be calculated.
>
> worksheets.Range("A1", Range("A1").Offset(colTOright)).Select
>
> Where colTOright will be calculated earlier. I tried just plugging in
> a number for colTOright but get an VBScript runtime error, Type
> mismatch: 'Range'
>
> Is there another way?
>
> Thanks
> Robert

**roblo** · 08-25-2005, 07:53 PM

Thanks Jake for the suggestion, but it does not work in my .VBS script. Maybe in VBA?

colTOright = 6
' Create a new Excel workbook
set objXL = WScript.CreateObject("Excel.Application")
objXL.Visible = True
set workbook = objXL.Workbooks.Add
set worksheet = workbook.Worksheets("sheet1")
' worksheet.Range("A1:F1").Select
With Worksheet("Sheet1")
.Range(.Range("A1"), .Range("A1").Offset(colTOright)).Select
End With

I'm just trying to replace the ending cell (F1) with a string or use an Offset. Didn't think it would be that difficult.

newColumn = .Cells(A1).Offset(colTOright).Value
and replace F1 with newColumn, but that object doesn't except strings.

worksheet.Range("A1:F1").Select

Robert

Jake Marx · 08-26-2005, 02:05 PM

Hi roblo,

I didn't realize you were automating Excel from VBScript. Your original
syntax was correct, as you have a worksheet object variable declared:

> With Worksheet("Sheet1")
> Range(.Range("A1"), .Range("A1").Offset(colTOright)).Select
> End With

Should be:

> With worksheet
> Range(.Range("A1"), .Range("A1").Offset(colTOright)).Select
> End With

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

roblo wrote:
> Thanks Jake for the suggestion, but it does not work in my .VBS
> script. Maybe in VBA?
>
> colTOright = 6
> ' Create a new Excel workbook
> set objXL = WScript.CreateObject("Excel.Application")
> objXL.Visible = True
> set workbook = objXL.Workbooks.Add
> set worksheet = workbook.Worksheets("sheet1")
> ' worksheet.Range("A1:F1").Select
> With Worksheet("Sheet1")
> Range(.Range("A1"), .Range("A1").Offset(colTOright)).Select
> End With
>
> I'm just trying to replace the ending cell (F1) with a string or use
> an Offset. Didn't think it would be that difficult.
>
> newColumn = .Cells(A1).Offset(colTOright).Value
> and replace F1 with newColumn, but that object doesn't except strings.
>
>
> worksheet.Range("A1:F1").Select
>
> Robert

**roblo** · 08-26-2005, 02:36 PM

Thanks Jake,

Final syntax that works after trial and error looks like this:

With worksheets
.Range(("A1"),.Range("A1").Offset(0,colTOright)).Select
End with

Had to specific Row and Column offsets for some reason.

thanks again

Robert

Tom Ogilvy · 08-26-2005, 04:05 PM

If he wants to expand to the right I would suggest the following
modification:

With worksheet
.Range(.Range("A1"), .Range("A1").Offset(0,colTOright)).Select
End With

--
Regards,
Tom Ogilvy

"Jake Marx" <[email protected]> wrote in message
news:[email protected]...
> Hi roblo,
>
> I didn't realize you were automating Excel from VBScript. Your original
> syntax was correct, as you have a worksheet object variable declared:
>
> > With Worksheet("Sheet1")
> > Range(.Range("A1"), .Range("A1").Offset(colTOright)).Select
> > End With
>
> Should be:
>
> > With worksheet
> > Range(.Range("A1"), .Range("A1").Offset(colTOright)).Select
> > End With
>
> --
> Regards,
>
> Jake Marx
> MS MVP - Excel
> www.longhead.com
>
> [please keep replies in the newsgroup - email address unmonitored]
>
>
> roblo wrote:
> > Thanks Jake for the suggestion, but it does not work in my .VBS
> > script. Maybe in VBA?
> >
> > colTOright = 6
> > ' Create a new Excel workbook
> > set objXL = WScript.CreateObject("Excel.Application")
> > objXL.Visible = True
> > set workbook = objXL.Workbooks.Add
> > set worksheet = workbook.Worksheets("sheet1")
> > ' worksheet.Range("A1:F1").Select
> > With Worksheet("Sheet1")
> > Range(.Range("A1"), .Range("A1").Offset(colTOright)).Select
> > End With
> >
> > I'm just trying to replace the ending cell (F1) with a string or use
> > an Offset. Didn't think it would be that difficult.
> >
> > newColumn = .Cells(A1).Offset(colTOright).Value
> > and replace F1 with newColumn, but that object doesn't except strings.
> >
> >
> > worksheet.Range("A1:F1").Select
> >
> > Robert
>

Tom Ogilvy · 08-26-2005, 06:05 PM

Love the time delayed excel forum.

--
Regards,
Tom Ogilvy

"roblo" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Jake,
>
> Final syntax that works after trial and error looks like this:
>
> With worksheets
> Range(("A1"),.Range("A1").Offset(0,colTOright)).Select
> End with
>
>
> Had to specific Row and Column offsets for some reason.
>
>
> thanks again
>
> Robert
>
>
> --
> roblo
> ------------------------------------------------------------------------
> roblo's Profile:
http://www.excelforum.com/member.php...o&userid=22469
> View this thread: http://www.excelforum.com/showthread...hreadid=399231
>

Jake Marx · 08-26-2005, 06:05 PM

Thanks, Tom!

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Tom Ogilvy wrote:
> If he wants to expand to the right I would suggest the following
> modification:
>
> With worksheet
> .Range(.Range("A1"), .Range("A1").Offset(0,colTOright)).Select
> End With
>
>
> "Jake Marx" <[email protected]> wrote in message
> news:[email protected]...
>> Hi roblo,
>>
>> I didn't realize you were automating Excel from VBScript. Your
>> original syntax was correct, as you have a worksheet object variable
>> declared:
>>
>>> With Worksheet("Sheet1")
>>> Range(.Range("A1"), .Range("A1").Offset(colTOright)).Select
>>> End With
>>
>> Should be:
>>
>>> With worksheet
>>> Range(.Range("A1"), .Range("A1").Offset(colTOright)).Select
>>> End With
>>
>> --
>> Regards,
>>
>> Jake Marx
>> MS MVP - Excel
>> www.longhead.com
>>
>> [please keep replies in the newsgroup - email address unmonitored]
>>
>>
>> roblo wrote:
>>> Thanks Jake for the suggestion, but it does not work in my .VBS
>>> script. Maybe in VBA?
>>>
>>> colTOright = 6
>>> ' Create a new Excel workbook
>>> set objXL = WScript.CreateObject("Excel.Application")
>>> objXL.Visible = True
>>> set workbook = objXL.Workbooks.Add
>>> set worksheet = workbook.Worksheets("sheet1")
>>> ' worksheet.Range("A1:F1").Select
>>> With Worksheet("Sheet1")
>>> Range(.Range("A1"), .Range("A1").Offset(colTOright)).Select
>>> End With
>>>
>>> I'm just trying to replace the ending cell (F1) with a string or use
>>> an Offset. Didn't think it would be that difficult.
>>>
>>> newColumn = .Cells(A1).Offset(colTOright).Value
>>> and replace F1 with newColumn, but that object doesn't except
>>> strings.
>>>
>>>
>>> worksheet.Range("A1:F1").Select
>>>
>>> Robert