Returning a range from an Offset from another range VBA

Question

How can I return a range from an function which includes an offset from another range?

This is my code (Function):

Private Function ProcessRange(rng) As Range
    If rng <> "A1" Then
        ProcessRange = Range(rng).Offset(r + 2)  //this is the problem
    Else
        ProcessRange = Range("A1")
    End If
End Function

From the example above, I'm trying to replace the range from the offset function. Is it possible?

Answer 1

There are several issues here:

• You should define the type of the parameters
• Use Set to set the return range
• You havn't defined what r is
• Offset needs two parameters, rows and columns
• Because you havn't specified a Worksheet, your function will return a Range on the Active Sheet

.

Private Function ProcessRange(rng As String) As Range
    If rng <> "A1" Then
        Set ProcessRange = Range(rng).Offset(2, 0) ' To Offset by 2 rows
    Else
        Set ProcessRange = Range("A1")
    End If
End Function