vba - 应用程序定义或对象定义的错误 VBA 在工作表之间复制

Question

我刚刚开始使用 VBA，在不同工作表之间复制数据时遇到了这个错误。

要求是找到特定的数据点 block 并将它们复制到单独的工作表上。

变量如 FirstRowArrayCol1包含数据点的位置，而 i表示列号。

错误出现在这一行

Set rngDest = wksDest.Range(Cells(i, firstPos))

请检查以下代码。非常感谢任何帮助，谢谢。

Set wksSource = ActiveWorkbook.Sheets("Sheet1")
Set wksDest = ActiveWorkbook.Sheets("Sheet2")
        For Each firstPos In FirstRowArrayCol1
            If firstPos = 0 Then
                Exit For
            End If
            For Each secondPos In SecondRowArrayCol1
                If secondPos = 0 Then
                    Exit For
                End If
                Diff = Abs(firstPos - secondPos)
                If Diff > 0 And Diff <= 5 Then
                    Debug.Print (column3 & firstPos & "," & column3 & secondPos & "," & column4 & firstPos & "," & column4 & secondPos)
                    'Copy Data
                    Set rngSource = wksSource.Range(Cells(i, firstPos), Cells(i + 1, secondPos))
                    rngSource.Range(Cells(i, firstPos), Cells(i + 1, secondPos)).Select
                    rngSource.Copy
                    'Paste Data Values
                    Set rngDest = wksDest.Range(Cells(i, firstPos))
                    rngDest.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

                End If
            Next secondPos
            For Each secondPos In SecondRowArrayCol2
                If secondPos = 0 Then
                    Exit For
                End If
                Diff = Abs(firstPos - secondPos)
                If Diff > 0 And Diff <= 5 Then
                    Debug.Print (column3 & firstPos & "," & column3 & secondPos & "," & column4 & firstPos & "," & column4 & secondPos)
                    'Copy Data
                    Set rngSource = wksSource.Range(Cells(i, firstPos), Cells(i + 1, secondPos))
                    rngSource.Range(Cells(i, firstPos), Cells(i + 1, secondPos)).Select
                    rngSource.Copy
                    'Paste Data Values
                    Set rngDest = wksDest.Range(Cells(i, firstPos))
                    rngDest.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

                End If
            Next secondPos
        Next firstPos

Answer 1

Worksheet.Range Property接受 2 个像

Set rngDest = wksDest.Range(Cells(i, firstPos), Cells(i, firstPos))

或一个地址

Set rngDest = wksDest.Range(Cells(i, firstPos).Address)

但不是 1 个细胞

Set rngDest = wksDest.Range(Cells(i, firstPos)) 'not valid

因为因此您可以使用 Cells直接地

Set rngDest = wksDest.Cells(i, firstPos)