Excel VBA运行时错误1004;我在代码中没有选择 [英] excel vba runtime error 1004; i don't have select in the code

问题描述

我正在尝试将一些输入复制到模型中，并将答案复制回第一张纸上.下面是代码. "data_sheet"是具有许多输入的工作表，"Final Model"给出了输出.非常简单；我正在尝试从中复制数据 将"data_sheet"转换为"final model"，然后将答案复制回去.当我尝试将答案复制回"data_sheet"时，出现错误1004.仅第一部分效果很好.

I am trying to copy some inputs to a model and copy back the answers to the first sheet. Below is the code. "data_sheet" is the sheet with many inputs and "Final Model" gives the out put. Its very simple; i am trying to copy data from "data_sheet" to "final model" and then copy the answer back. I am getting error 1004 when i try to copy back answer to "data_sheet". the first part alone works good.

Private Sub CommandButton1_Click()


    Dim wksSource As Worksheet, wksDest As Worksheet
    Dim rngStart As Range, rngSource As Range, rngDest As Range
    Dim rngStart2 As Range, rngSource2 As Range, rngDest2 As Range

For i = 1 To 6

    Set wksSource = ActiveWorkbook.Sheets("data_sheet")
    Set wksDest = ActiveWorkbook.Sheets("Final Model")

    Set rngSource = wksSource.Range(Cells(5, 3 + i - 1), Cells(23, 3 + i - 1))
    'Paste Data Values
    Set rngDest = wksDest.Range("C14")
    rngSource.Copy
    rngDest.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


'Till this code works. Now i just trying to reverse copy

    Set rngSource2 = wksDest.Range(Cells(38, 4), Cells(40, 4))
    Set rngDest2 = wksSource.Range(Cells(29, 3 + i - 1), Cells(31, 3 + i - 1))
    rngSource2.Copy
    rngDest2.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Next i

推荐答案

出现错误的原因是因为您试图使用 Active 中的Cells在wksDest上定义范围. >工作表，除非wksDest处于活动状态，否则它将始终失败.所以这句话:

The reason you get the error is because you're trying to define a range on wksDest using Cells from the Active worksheet, which will always fail unless wksDest is active. So this statement:

Set rngSource2 = wksDest.Range(Cells(38, 4), Cells(40, 4))

实际上是这样说的:

Set rngSource2 = wksDest.Range(ActiveSheet.Cells(38, 4), ActiveSheet.Cells(40, 4))

并且由于不能使用不同工作表上的单元格在一个工作表上定义范围(没有采取其他特殊措施)，为避免这种情况，可以使用With块，如下所示:

And since a range can't be defined on one sheet using cells on a different sheet (without taking other extraordinary measures), to avoid that, you can use With block like so:

With wksDest
    Set rngSource2 = .Range(.Cells(38, 4), .Cells(40, 4))
End With
With wksSource
    Set rngDest2 = .Range(.Cells(29, 3 + i - 1), .Cells(31, 3 + i - 1))
End With

或更直接地使用Resize方法:

Set rngSource2 = wksDest.Cells(38,4).Resize(3)
Set rngDest2 = wksSource.Cells(29, 3 + i - 1).Resize(3)

以上两种方法都避免使用不合格的Cells对象，这可能是导致1004错误的原因.

Both above approaches avoid the unqualified Cells object which is a likely cause of 1004 error.

此外，由于您仅复制值，因此可以执行简单的值分配，而不是copy和pastespecial，如下所示:

Also, since you're only copying values, you can do simple value assignment instead of copy and pastespecial, like so:

rngDest2.Value = rngSource2.Value

这篇关于Excel VBA运行时错误1004;我在代码中没有选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！