Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving into a cell range using Offset and LBound
I need to copy a formula to an empty column on a number of spreadsheets.
Ihave brokendown the task in the following 3 steps: 1. The only reference that I have about the size of each range in the empty column is the data entered on the previous column. So I capture the known range by: Dim VarT as Variant VarT = Range("a1").Select Range(Selection, Selection.End(xlDown)).Select 2. Since I need range VarT to be located in cell B1 (start of range with VarT dimensions) I have tried the following: Dim FinalRange as Range Set FinalRange = Selection.Offset(0, 1).Range(LBound(VarT)).Select With the idea that this will displace me to the start of the required range. 3. Where I can finally code formula as: ActiveCell.FormulaR1C1 = "=********" FinalRange.Select Selection.AutoFill Destination:=FinalRange, Type:=xlFillDefault FinalRange.Select At the moment I am not able to pass step 2. For some reason I am not able to move into the final range required. Offset only works with cell therefore i thought that using Lbound will get me starting position of VarT range and be able to be displaced using Offset. But no luck so far Is my thinking correct? If someone can help I would appreciate it |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving into a cell range using Offset and LBound
You can probably do it in one stage:
Defining your range using it's first and last cells first cell: row = 1 column = 2 last cell: row = cells(65536,1).end(xlup).row column=2 'so you end up with range(cells(1,2),cells(cells(65536,1).end(xlup).ro w,2).formula="=whatever" "TISR" wrote: I need to copy a formula to an empty column on a number of spreadsheets. Ihave brokendown the task in the following 3 steps: 1. The only reference that I have about the size of each range in the empty column is the data entered on the previous column. So I capture the known range by: Dim VarT as Variant VarT = Range("a1").Select Range(Selection, Selection.End(xlDown)).Select 2. Since I need range VarT to be located in cell B1 (start of range with VarT dimensions) I have tried the following: Dim FinalRange as Range Set FinalRange = Selection.Offset(0, 1).Range(LBound(VarT)).Select With the idea that this will displace me to the start of the required range. 3. Where I can finally code formula as: ActiveCell.FormulaR1C1 = "=********" FinalRange.Select Selection.AutoFill Destination:=FinalRange, Type:=xlFillDefault FinalRange.Select At the moment I am not able to pass step 2. For some reason I am not able to move into the final range required. Offset only works with cell therefore i thought that using Lbound will get me starting position of VarT range and be able to be displaced using Offset. But no luck so far Is my thinking correct? If someone can help I would appreciate it |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving into a cell range using Offset and LBound
Many Thanks Rob
Your use of cells and range is more efficient. "Rob" wrote: You can probably do it in one stage: Defining your range using it's first and last cells first cell: row = 1 column = 2 last cell: row = cells(65536,1).end(xlup).row column=2 'so you end up with range(cells(1,2),cells(cells(65536,1).end(xlup).ro w,2).formula="=whatever" "TISR" wrote: I need to copy a formula to an empty column on a number of spreadsheets. Ihave brokendown the task in the following 3 steps: 1. The only reference that I have about the size of each range in the empty column is the data entered on the previous column. So I capture the known range by: Dim VarT as Variant VarT = Range("a1").Select Range(Selection, Selection.End(xlDown)).Select 2. Since I need range VarT to be located in cell B1 (start of range with VarT dimensions) I have tried the following: Dim FinalRange as Range Set FinalRange = Selection.Offset(0, 1).Range(LBound(VarT)).Select With the idea that this will displace me to the start of the required range. 3. Where I can finally code formula as: ActiveCell.FormulaR1C1 = "=********" FinalRange.Select Selection.AutoFill Destination:=FinalRange, Type:=xlFillDefault FinalRange.Select At the moment I am not able to pass step 2. For some reason I am not able to move into the final range required. Offset only works with cell therefore i thought that using Lbound will get me starting position of VarT range and be able to be displaced using Offset. But no luck so far Is my thinking correct? If someone can help I would appreciate it |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum variable range of column entries in offset cell | New Users to Excel | |||
Sum variable range of column entries in offset cell | New Users to Excel | |||
Offset Function works in cell, not in named range | Excel Worksheet Functions | |||
Selecting a range offset from a cell | Excel Programming | |||
Selecting cell using range.offset | Excel Programming |