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

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

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