title: VBA: Getting run-time 1004: Method 'Range' of object '_Worksheet' failed when using cells
body: <p>I have 2 worksheets: Assets and Overview.</p>
  
  <p>The functions are all put in a module.</p>
  
  <pre><code>Public Function GetLastNonEmptyCellOnWorkSheet(Ws As Worksheet, Optional sName As String = "A1") As Range
     Dim lLastRow        As Long
     Dim lLastCol        As Long
     Dim rngStartCell    As Range
  
     Set rngStartCell = Ws.Range(sName)
     lLastRow = Ws.Cells.Find(What:="*", After:=Ws.Range(rngStartCell), LookIn:=xlFormulas, _
             Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
             MatchCase:=False).Row
  
     lLastCol = Ws.Cells.Find(What:="*", After:=Ws.Range(rngStartCell), LookIn:=xlFormulas, _
             Lookat:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
             MatchCase:=False).Column
  
     Set GetLastNonEmptyCellOnWorkSheet = Ws.Range(Ws.Cells(lLastRow, lLastCol))
  End Function
  </code></pre>
  
  <p>From the worksheet Overview I call:</p>
  
  <pre><code>   Set RngAssets = GetLastNonEmptyCellOnWorkSheet(Worksheets("Assets"), "A1")
  </code></pre>
  
  <p>But I always get the error: </p>
  
  <blockquote>
    <p>VBA: Getting run-time 1004: Method 'Range' of object '_Worksheet'
    failed</p>
  </blockquote>
  
  <p>on the line:</p>
  
  <pre><code> Set GetLastNonEmptyCellOnWorkSheet = Ws.Range(Ws.Cells(lLastRow, lLastCol))
  </code></pre>
  
  <p>There is data on the worksheet Assets. The last used cell is W9 (lLastRow = 9 and lLastCol = 23).</p>
  
  <p>Any idea why this is not working?</p>