Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with Range.Cells.Offset and Range.Cells( row + offset, column) | Excel Programming | |||
Select row (with offset) from VB formula result | Excel Programming | |||
How to Select a relative range with Using "Find" and Offset() | Excel Discussion (Misc queries) | |||
Select Range - using Offset ( 40 ,0 ) - not working | Excel Programming | |||
Select and Copy Range using Offset | Excel Programming |