Cannot return the address of a cell range from one function to another, I get an 'object required' error

I get an 'object required' error when I try to return the value of a range from one function to another function. I read the other answers about 'object required' but I cannot figure out how that relates to my code. Forgive me but I'm new to VBA. Here's my code:

Function functionOne()
    Dim r As Range
    Workbooks("workbook1.xls").Activate
    Sheets("sheet1").Select
    Set r = GetRange("A1")
    Application.Goto r
End Function

Function GetRange(strStart As String) As Variant
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Set rng1 = Cells.Find("*", [A1], , , xlByRows, xlPrevious)
    Set rng2 = Cells.Find("*", [A1], , , xlByColumns, xlPrevious)
    Set rng3 = Range([strStart], Cells(rng1.Row, rng2.Column))
    GetRange = rng3.Value
End Function

The 'object required' error happens at the Set r = GetRange("A1")in functionOne(). Thanks for your help.

Answers


Change your GetRange function to return Range object:

Function GetRange(strStart As String) As Range
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Set rng1 = Cells.Find("*", [A1], , , xlByRows, xlPrevious)
    Set rng2 = Cells.Find("*", [A1], , , xlByColumns, xlPrevious)
    Set rng3 = Range([strStart], Cells(rng1.Row, rng2.Column))
    Set GetRange = rng3
End Function

Need Your Help

WCF: I want to use an authentication token like the Google map API. How can I learn more about this?

wcf web-services .net-3.5

I want to allow access to a WCF service to only those requests that contain a developer key. This is similiar to how the Google Maps API works. Register for a developer key and include that key in ...