Range(Cells())中的运行时错误1004 [英] Run time error 1004 in Range(Cells())

问题描述

我在多个宏中包含以下代码，但是它吸收了很多我不需要的信息.公式如下:

I have the following code in a number of macros, but it takes in a lot of information, which I don't need. The formula is as follows:

Range("A1" & h & ":BD" & h).Value = Worksheets("ABC1").Range("A" & h & ":BD" & h).Value

我尝试将公式更改为更具动态性的版本，如下所示:

I have tried to change the formula to a more dynamic version, as follows:

Sub MFGI_NAV()

Dim x As Variant
Dim y As Variant

    Worksheets("ABC1").Activate

    h = Cells(Rows.Count, "A").End(xlUp).Row

    Set WS = Sheets.Add(before:=Worksheets("ZYX"), Type:=xlWorksheet)
    ActiveSheet.Name = "JKL"

    x = 1
    y = 1
    While y <= 82

        Cells(1, x).Select
        Range(Cells(1, x), Cells(h, x)).Value = Worksheets("ABC1").Range(Cells(1, y), Cells(h, y)).Value

        x = x + 1
        y = y + 3

    Wend
    End Sub

此公式仅吸收工作表"ABC1"中的每个第三列，并将其放入"JKL"中的每个增量列中.

This formula only takes in every 3rd column in the sheet "ABC1" and put it into every incremental column in "JKL".

但是，当我尝试这样做时，出现运行时错误1004.

However, when I try this, I get a run time error 1004.

关于如何使它更动态地工作有什么建议吗?

Is there any suggestions on how I can get this to work more dynamically?

顺便说一句，我问的原因是，尽管此文件只有82列和2万行，但以后的工作表可能有多达1万列和50万行(是的，我知道这是很大的，但这就是系统生成信息，而只需要其中的1/3)

BTW, the reason I ask is that while this file only has 82 columns, and 20k rows, future sheets could have up to 10k columns and 500k rows (and yes, I know that is massive, but that is the way the system generates the information, and only 1/3 of it is needed)

推荐答案

Range(Cells(1，x)，Cells(h，x)).Value =工作表("ABC1").Range(Cells(1，y)，Cells(h，y)).Value

Range(Cells(1, x), Cells(h, x)).Value = Worksheets("ABC1").Range(Cells(1, y), Cells(h, y)).Value

由于您的单元格对象不完全合格，您遇到了该错误.

You are getting that error because your cells objects are not fully qualified.

尝试

WS.Range(WS.Cells(1, x), WS.Cells(h, x)).Value = _
Worksheets("ABC1").Range(Worksheets("ABC1").Cells(1, y), Worksheets("ABC1").Cells(h, y)).Value

同样，请限定代码中的所有对象.

Similarly please qualify all your objects in your code.

这篇关于Range(Cells())中的运行时错误1004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！