Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I add an apostrophy to a range of existing cells? | Excel Worksheet Functions | |||
Delete Rows based on not existing in range | Excel Programming | |||
Create/copy combo boxes in one range if condition is met in a different range | Excel Programming | |||
Add a Range to an existing Range in a loop? | Excel Programming | |||
Open only existing Excel Files in a given Range | Excel Programming |