Create a new range from existing range
 

Hi,

I am writing a user defined function with a range as one of the
arguments e.g. N4:N14. While executing the function, I need to find
out cells in column x units away e.g. when x = 1 output should be
O4:14 or when x =2 output should P4:P14. I need to assign new range to
a variable of type range.

I am new to VBA. Can you help me?

Sachchi

Create a new range from existing range
 

Sounds like you are looking for Offset

Sub test()
Dim sAddr as string
Dim rng As Range
Dim nOffRows As Long, nOffCols As Long

Set rng = Range("A1:A10")

nOffRows = 0
nOffCols = 2
sAddr = rng.Offset(nOffRows, nOffCols).Address(0, 0)

MsgBox sAddr

End Sub

Keep in mind if the Offset tries to take the range off the sheet it will
error.

Regards,
Peter T


"Sachchi" wrote in message
...
Hi,

I am writing a user defined function with a range as one of the
arguments e.g. N4:N14. While executing the function, I need to find
out cells in column x units away e.g. when x = 1 output should be
O4:14 or when x =2 output should P4:P14. I need to assign new range to
a variable of type range.

I am new to VBA. Can you help me?

Sachchi