vba - 如何将范围复制到临时工作簿中并使用 vba 函数返回对其的引用？

Question

我在“rTemp.Value = vaTemp”行上出现以下错误。我在这里做错了什么？我走在正确的道路上吗？

Function CreateTempRange(rSource As range) As range
    ' Declarations
    Dim rTemp As range
    Dim vaTemp As Variant
    Dim wsTemp As Worksheet
    Dim wbTemp As Workbook

    ' Open temp worksheet
    Set wbTemp = Workbooks.Add
    Set wsTemp = wbTemp.Worksheets.Add

    ' Copy range into it and get a reference to the temp range
    vaTemp = rSource.Value
    Set rTemp = wsTemp.range("A1").Resize(UBound(vaTemp, 1), UBound(vaTemp, 2))
    rTemp.Value = vaTemp

    ' Return the temp range
    Set CreateTempRange = rTemp
End Function

注意:此函数旨在供其他函数使用，而不是直接从单元格调用。

Answer 1

Set rTemp = wsTemp.range("A1").Resize(UBound(vaTemp, 1), UBound(vaTemp, 2)

这里会出现类型不匹配...我不确定它是否真的有意义。 ubound(a,2) 用于多维数组而不是范围。

我猜您想要获取指定单元格中的值，然后根据它的值将其复制多次。那是对的吗？

希望下面的内容能为您提供一个可以使用的示例。如果没有编辑您的帖子，我会看看是否可以提供帮助。

Function CreateTempRange(rSource As Range) As Range
    '' Declarations
    Dim rTemp As Range
    Dim vaTemp As Variant

    Dim wsTemp As Worksheet
    Dim wbTemp As Workbook

    '' Open temp worksheet
    Set wbTemp = Workbooks.Add
    Set wsTemp = wbTemp.Worksheets.Add

    '' Copy range into it and get a reference to the temp range
    vaTemp = rSource.Value
    ''Set rTemp = wsTemp.Range("A1").Resize(UBound(vaTemp, 1), UBound(vaTemp, 2))

    Dim iTemp As Integer
    On Error Resume Next
    iTemp = CInt(vaTemp)
    On Error GoTo 0

    If iTemp < 1 Then
      iTemp = 1
    End If
    Set rTemp = wsTemp.Range("A1:A" & iTemp)
    rTemp.Value = vaTemp

    '' Return the temp range
    Set CreateTempRange = rTemp
End Function

Sub test()

  Dim r As Range
  Dim x As Range
  Set r = ActiveSheet.Range("A1")
  Set x = CreateTempRange(r)

End Sub