具有下标超出范围错误的Excel VBA剪切和粘贴功能

debugcn 发表于 Dev

瑞奇

    Sub CutandPaste()
    With ActiveSheet
        Dim i As Long
        Dim Sheet1 As Worksheet
        Dim Sheet2 As Worksheet

        Set mainsheet = ActiveWorkbook.Sheets("Sheet1")
        Set subsheet = ActiveWorkbook.Sheets("Sheet2")

        endrow = mainsheet.Range("A" & mainsheet.Rows.Count).End(xlUp).Row

            For i = 25 To endrow
                If mainsheet.Cells(i, "J") <> "" Or mainsheet.Cells(i, "L") <> "" Or mainsheet.Cells(i, "N") <> "" Then
                    mainsheet.Range(Cells(i, "B"), Cells(i, "O")).Cut Destination:=subsheet.Range(Cells(i + 25, "B"), Cells(i + 25, "O")).Paste
                    mainsheet.Range(Cells(i, "B"), Cells(i, "O")).Delete '~~> if you want to delete
                    i = i + 1
                End If
            Next
    End With
End Sub

我想用以下功能编写程序

如果Sheet1中的单元格（“ J” i）或单元格（“ L” i）或单元格（“ N” i）不为空，则i等于任何整数

然后在工作表1中将Cell（“ B” i）切割为Cell（“ O” i）到Sheet2中的Cell（“ B” i + 25）切割为Cell（“ O” i + 25），其中i等于任何整数

但是，据说我的代码超出范围。

为什么会这样，我该如何解决？

非常感谢

    Sub CutandPaste()
    With ActiveSheet
        Dim i As Long
        Dim Sheet1  As Worksheet
        Dim Sheet2 As Worksheet

        Set mainsheet = ActiveWorkbook.Sheets("Sheet1")
        Set subsheet = ActiveWorkbook.Sheets("Sheet2")

        endrow = mainsheet.Range("A" & mainsheet.Rows.Count).End(xlUp).Row

        For i = endrow To 25 Step -1

                If mainsheet.Cells(i, "J") <> "" Or mainsheet.Cells(i, "L") <> "" Or mainsheet.Cells(i, "N") <> "" Then
                    mainsheet.Range(mainsheet.Cells(i, "B"), mainsheet.Cells(i, "O")).Cut Destination:=subsheet.Range(subsheet.Cells(i + 56, "B"), subsheet.Cells(i + 56, "O")).Paste
                    mainsheet.Range(mainsheet.Cells(i, "B"), mainsheet.Cells(i, "O")).Delete '~~> if you want to delete

                End If
            Next
    End With
End Sub

不正确的

mainsheet.Range（mainsheet.Cells（i，“ B”），mainsheet.Cells（i，“ O”））。剪切目标：= subsheet.Range（subsheet.Cells（i + 56，“ B”），subsheet。单元格（i + 56，“ O”））。粘贴

正确的

mainsheet.Range（mainsheet.Cells（i，“ B”），mainsheet.Cells（i，“ O”））。剪切目标：= subsheet.Cells（i + 56，“ B”）

我使用子范围重构了您的代码。我认为它看起来更干净。

这是定义相同范围的一些不同方法

Range（Cells（i，“ A”），Cells（i，“ O”））Cells（i，“ A”）。Resize（1,15）Rows（i）.Range（“ A1：O1”）Rows（ i）.Range（“ A1：O1”）Rows（i）.Resize（1,15）Rows（2）.Columns（“ A：O”）

Sub CutandPaste()

    Dim i As Long
    Dim mainsheet As Worksheet
    Dim subsheet As Worksheet

    Set mainsheet = ActiveWorkbook.Sheets("Sheet1")
    Set subsheet = ActiveWorkbook.Sheets("Sheet2")

    With mainsheet
        endrow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = endrow To 25 Step -1
            If .Cells(i, "J") <> "" Or .Cells(i, "L") <> "" Or .Cells(i, "N") <> "" Then
                .Rows(i).Resize(1, 15).Cut Destination:=subsheet.Cells(i + 56, "B")
                .Rows(i).Resize(1, 15).Delete    '~~> if you want to delete
            End If
        Next
    End With
End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。