VBA：从函数问题返回一个范围 [英] VBA: returning a range from a function issue

问题描述

我正在清理我的代码，并且有一段代码用于查找最后一个单元格。这用于多个工作表。当我把它变成一个函数时，它似乎会导致错误。我相信它是我返回似乎是问题的范围的方式。我得到一个Object Required的运行时错误在下面的代码中，我试图通过捕获不同表中的所有非空白单元格来创建数据透视表。

  Dim pt As PivotTable 
 Dim cacheOfPt As如果任何人有提示我出错的建议， PivotCache'这是pt 
的源数据Dim pf As PivotField 
 Dim pi由于PivotItem的透视项目是特定透视图的价值
 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发行
  

函数为最后一格

 函数myRangeFunc（ws作为工作表）作为范围
 
 
对错误继续下一个
用ws 
 Set LastRow = Cells.Find *，[A1]，，，xlByRows，xlPrevious）
设置la stCol = Cells.Find（*，[A1]，，，xlByColumns，xlPrevious）
 End With 
 
如果Not LastRow是Nothing然后
 Set myRangeFunc = Range（[ A1]，Cells（LastRow.Row，lastCol.Column））
 Debug.PrintRange is& myRangeFunc.Address（0，0）
 Else 
 MsgBox表单为空，vbCritical 
 End If 
 
 End Function 
  Cells 方法返回一个范围对象。 

解决方案 ，它的默认属性是 .Value ，所以当你这样做时：

  Set myRangeFunc = Range（[A1]，Cells（LastRow.Row，lastCol.Column））
  

  Set myRangeFunc = Range（[A1]，Cells（LastRow.Row，lastCol。 Column）.Value）
  

哪一个总是失败一个有效的地址字符串，如$ A $ 1等）。

代替：

 Set myRangeFunc = Range（[A1] .Address，Cells（LastRow.Row，lastCol.Column）.Address）
  

Re 424错误，我认为如果 LastRow 或 LastCol 是 Nothing （即没有找到数据）。

清理这个函数，正确地声明你的变量并清除On Error Resume Next ：

 函数myRangeFunc（ws作为工作表）作为范围
 Dim LastRow作为范围，LastCol作为范围
 Dim r作为长，c作为长
 
随着ws 
 Set LastRow = Cells.Find（*，[A1]，，，xlByRows，xlPrevious）
 Set lastCol = Cells.Find（*，[A1]，，，xlByColumns，xlPrevious）
如果LastRow是Nothing然后
r = 1 
否则：r = LastRow.Row 
 End If 
如果LastCol是Nothing然后
c = 1 
否则： c = LastCol.Column 
 End If 
 
 End With 
 
 
 Set myRangeFunc = Range（A1，Cells（r，c）.Address ）
 
如果LastRow是Nothing并且LastCol是Nothing MsgBox表单空白！ 
 
结束功能
  

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

解决方案

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

这篇关于VBA：从函数问题返回一个范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！