选择要复制的范围时出错 [英] Error in selecting range to copy

问题描述

我需要选择一个特定范围以在每个for循环中使用可变的起点和终点进行复制，因此使用了Range(Cells(x，x)，Cells(y，y).Select方法.这会给我一个错误:

I need to select a specific range to copy with variable start and end point at each for loop, so used the Range(Cells(x,x), Cells(y,y).Select method. This would give me an error:

For i = 1 To 40
        Worksheets("BloombergData").Select
        Worksheets("BloombergData").Range(Cells(5, 2 + 11 * (i - 1)), Cells(4 + Worksheets("Lookup").Cells(i + 1, 3).Value, 11 + 11 * (i - 1))).Select
        Selection.Copy
        Worksheets("Data_Rearranged").Range(Cells(6 + Worksheets("Lookup").Cells(i, 3).Value, 4), Cells(5 + Worksheets("Lookup").Cells(i + 1, 3).Value, Data_Columns + 3)).Select
        Selection.Paste
    Next i

即使我简化了单元格功能的内容，它仍然没有起作用.我尝试进行此测试，并给了我同样的错误:

Even when I simplified what goes into the cells function, it still didn't work. I tried this to test and it gave me the same error:

Worksheets("BloombergData").Range(Cells(1, 1), Cells(2, 1)).Select
Selection.Copy

仅当我将实际单元格放到Range中时才起作用，即Range("A1")

It only worked when I put the actual cell in the Range, i.e. Range("A1")

但是我的范围是可变的，所以我需要找到一些选择可变范围的方法.

But my range is variable so I need to find some way of selecting a variable range.

请帮助?

推荐答案

很明显，您会收到错误.您的单元格对象不完全合格.请注意以下代码中Cells对象之前的DOT.

It is but obvious that you will get the error. Your Cells Object is not fully Qualified. Notice the DOT before the Cells Object in the code below.

顺便说一句，我尚未测试 2 + 11 *(i-1)的计算结果.因此，如果计算得出的数字不可接受，则代码将再次失败.

BTW I have not tested what 2 + 11 * (i - 1) evaluates to. So if evaluates to an unacceptable number then the code will fail again.

Sub Sample()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("BloombergData")

    With ws
        For i = 1 To 40
            .Range(.Cells(5, 2 + 11 * (i - 1)), .Cells(4 + Worksheets("Lookup").Cells(i + 1, 3).Value, 11 + 11 * (i - 1))).Copy
            '
            '~~> Rest of the code
            '
        Next
    End With
End Sub

有趣的阅读

为便于理解，您还可以将代码分解为该代码.以后调试它会变得很容易:)

For easy understanding you could also break down your code to this. It will become easy to debug it later :)

Sub Sample()
    Dim ws As Worksheet
    Dim r1 As Long, c1 As Long
    Dim r2 As Long, c2 As Long

    Set ws = ThisWorkbook.Worksheets("BloombergData")

    With ws
        For i = 1 To 40
            r1 = 5
            c1 = 2 + 11 * (i - 1)

            r2 = 4 + Worksheets("Lookup").Cells(i + 1, 3).Value
            c2 = 11 + 11 * (i - 1)

            .Range(.Cells(r1, c1), .Cells(r2, c2)).Copy

            '
            '~~> Rest of the code
            '
        Next
    End With
End Sub

这篇关于选择要复制的范围时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！