VBA equivalent to =OFFSET(....
 

I want to create a range in VBA in a similar fashion to the worksheet
function OFFSET. E.g..

=Offset(MyNamedRange,10,1,0,-5)

Which would give me a 10 x 1 range, 5 columns to the left of
"MyNamedRange" (which is a single cell).

How would I do this in VBA? I intend to use this range and another
range in the VBA 'Intersect' function to create a third range.

My expereimental code is something like this (after declaring and
setting the various variables)...

TestVals = Application.Offset(Cyc.Range("CurrentPowerDist"), 0, -6, r,
0)
MaxVal = Cyc.Range("CurrentPowerDist").Offset(-2, -6)
MaxRng = Intersect(TestVals, MaxVal)

Thanks
Nick

VBA equivalent to =OFFSET(....
 

Sub offset()
Dim MyRange As Range
Dim NewRange As Range
Set MyRange = ActiveCell
rw = MyRange.Row
cl = MyRange.Column - 5
Set NewRange = Range(Cells(rw, cl), Cells(rw + 10, cl))
NewRange.Select
End Sub

--
Gary''s Student - gsnu200856


"Nick_F" wrote:

I want to create a range in VBA in a similar fashion to the worksheet
function OFFSET. E.g..

=Offset(MyNamedRange,10,1,0,-5)

Which would give me a 10 x 1 range, 5 columns to the left of
"MyNamedRange" (which is a single cell).

How would I do this in VBA? I intend to use this range and another
range in the VBA 'Intersect' function to create a third range.

My expereimental code is something like this (after declaring and
setting the various variables)...

TestVals = Application.Offset(Cyc.Range("CurrentPowerDist"), 0, -6, r,
0)
MaxVal = Cyc.Range("CurrentPowerDist").Offset(-2, -6)
MaxRng = Intersect(TestVals, MaxVal)

Thanks
Nick

VBA equivalent to =OFFSET(....
 

When using intersect don't forget to use SET

from
MaxRng = Intersect(TestVals, MaxVal)
to
set MaxRng = Intersect(TestVals, MaxVal)


"Nick_F" wrote:

I want to create a range in VBA in a similar fashion to the worksheet
function OFFSET. E.g..

=Offset(MyNamedRange,10,1,0,-5)

Which would give me a 10 x 1 range, 5 columns to the left of
"MyNamedRange" (which is a single cell).

How would I do this in VBA? I intend to use this range and another
range in the VBA 'Intersect' function to create a third range.

My expereimental code is something like this (after declaring and
setting the various variables)...

TestVals = Application.Offset(Cyc.Range("CurrentPowerDist"), 0, -6, r,
0)
MaxVal = Cyc.Range("CurrentPowerDist").Offset(-2, -6)
MaxRng = Intersect(TestVals, MaxVal)

Thanks
Nick