title: VBA: returning a range from a function issue
body: <p>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?</p>
  
  <pre><code>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))'&lt;= this line is the issue
  </code></pre>
  
  <p>function for last cell</p>
  
  <pre><code>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 " &amp; myRangeFunc.Address(0, 0)
          Else
              MsgBox "sheet is blank", vbCritical
          End If
  
  End Function
  </code></pre>