Create a new range from existing range

[Sachchi]

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 P414. I need to assign new range to
a variable of type range.

I am new to VBA. Can you help me?

Sachchi

 

[Peter T]

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