CODEWITHSUNDEEP
functionvbaparameters
Calgar99
Calgar99

Reputation: 1688

VBA: returning a range from a function issue

I am trying to clean up my code and there is a snippet which I use to find the last cell. This is used for multiple worksheets. When i turn this into a function it appears to cause an error. I believe its the way I return the range which appears to be the issue. I get a run time error of "Object Required" In the code below I am trying to create a pivot table by capturing all non blanks cells from a different table. If anyone has suggestions where I am going wrong it would be much appreciated?

Dim pt As PivotTable
Dim cacheOfPt As PivotCache 'this is the source data for the pt
Dim pf As PivotField
Dim pi As PivotItem 'pivot item are the values of a particular pivot feld
Dim myRange As Range
Dim wsPivot As Worksheet
Dim wsJournal As Worksheet

Set wsJournal = Worksheets("Sales_Journals")
Set myRange = myRangeFunc(wsJournal)

Worksheets.Add.Name = "UA.01.01 Breakdown per Product"
wsJournal.Select
Set cacheOfPt = ActiveWorkbook.PivotCaches.Create(xlDatabase, myRange.Address(0, 0))'<= this line is the issue

function for last cell

Function myRangeFunc(ws As Worksheet) As Range


        On Error Resume Next
        With ws
            Set LastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious)
            Set lastCol = Cells.Find("*", [A1], , , xlByColumns, xlPrevious)
        End With

        If Not LastRow Is Nothing Then
            Set myRangeFunc = Range([A1], Cells(LastRow.Row, lastCol.Column))
            Debug.Print "Range is " & myRangeFunc.Address(0, 0)
        Else
            MsgBox "sheet is blank", vbCritical
        End If

End Function

Upvotes: 0

Views: 84

Answers (1)

David Zemens
David Zemens

Reputation: 53623

The Cells method returns a range object, the default property of which is its .Value, so when you do this:

Set myRangeFunc = Range([A1], Cells(LastRow.Row, lastCol.Column))

You are actually doing this:

Set myRangeFunc = Range([A1], Cells(LastRow.Row, lastCol.Column).Value)

Which will always fail (unless that cell contains a valid address string, like "$A$1", etc.).

Do this instead:

Set myRangeFunc = Range([A1].Address, Cells(LastRow.Row, lastCol.Column).Address)

Re the 424 error, I would expect that if either of the LastRow or LastCol are Nothing (i.e., there is no data found).

Clean up the function a bit and properly declare your variables and get rid of On Error Resume Next:

Function myRangeFunc(ws As Worksheet) As Range
    Dim LastRow as Range, LastCol as Range
    Dim r as Long, c as Long

        With ws
            Set LastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious)
            Set lastCol = Cells.Find("*", [A1], , , xlByColumns, xlPrevious)
            If LastRow Is Nothing Then
                r = 1
            Else: r = LastRow.Row
            End If
            If LastCol Is Nothing Then
                c = 1
            Else: c = LastCol.Column
            End If

        End With


        Set myRangeFunc = Range("A1", Cells(r, c).Address)

        If LastRow Is Nothing And LastCol Is Nothing Then MsgBox "Sheet is blank!"

End Function

Upvotes: 1

Related Questions