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

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
------------------------------------------------------------------------
roblo's Profile: http://www.excelforum.com/member.php...o&userid=22469
View this thread: http://www.excelforum.com/showthread...hreadid=399231

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

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

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

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
------------------------------------------------------------------------
roblo's Profile: http://www.excelforum.com/member.php...o&userid=22469
View this thread: http://www.excelforum.com/showthread...hreadid=399231

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

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

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

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

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

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" wrote in message
...
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

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

Love the time delayed excel forum.

--
Regards,
Tom Ogilvy

"roblo" wrote in
message ...

Thanks Jake,

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

With worksheets
Range(("A1"),.Range("A1").Offset(0,colTOright)).Se lect
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

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

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" wrote in message
...
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