Excel VBA：循环单元格并将值复制到另一个工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10782189/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA: Loop through cells and copy values to another workbook
提问by Patrick
I already spent hours on this problem, but I didn't succeed in finding a working solution.
我已经在这个问题上花了几个小时，但我没有成功找到一个可行的解决方案。
Here is my problem description:
这是我的问题描述：
I want to loop through a certain range of cells in one workbook and copy values to another workbook. Depending on the current column in the first workbook, I copy the values into a different sheet in the second workbook.
When I execute my code, I always get the runtime error 439: object does not support this method or property.
我想遍历一个工作簿中的特定范围的单元格并将值复制到另一个工作簿。根据第一个工作簿中的当前列，我将值复制到第二个工作簿中的不同工作表中。当我执行我的代码时，我总是得到runtime error 439: object does not support this method or property.
My codelooks more or less like this:
我的代码看起来或多或少是这样的：
Sub trial()
Dim Group As Range
Dim Mat As Range
Dim CurCell_1 As Range
Dim CurCell_2 As Range
Application.ScreenUpdating = False
Set CurCell_1 = Range("B3") 'starting point in wb 1
For Each Group in Workbooks("My_WB_1").Worksheets("My_Sheet").Range("B4:P4")
Set CurCell_2 = Range("B4") 'starting point in wb 2
For Each Mat in Workbooks("My_WB_1").Worksheets("My_Sheet").Range("A5:A29")
Set CurCell_1 = Cells(Mat.Row, Group.Column) 'Set current cell in the loop
If Not IsEmpty(CurCell_1)
Workbooks("My_WB_2").Worksheets(CStr(Group.Value)).CurCell_2.Value = Workbooks("My_WB_1").Worksheets("My_Sheet").CurCell_1.Value 'Here it break with runtime error '438 object does not support this method or property
CurCell_2 = CurCell_2.Offset(1,0) 'Move one cell down
End If
Next
Next
Application.ScreenUpdating = True
End Sub
I've done extensive research and I know how to copy values from one workbook to another if you're using explicit names for your objects (sheets & ranges), but I don't know why it does not work like I implemented it using variables. I also searched on stackoverlow and -obviously- Google, but I didn't find a similar problem which would answer my question.
我已经进行了广泛的研究，如果您为对象（工作表和范围）使用显式名称，我知道如何将值从一个工作簿复制到另一个工作簿，但我不知道为什么它不像我使用的那样工作变量。我还搜索了 stackoverlow 和 - 显然 - 谷歌，但我没有找到可以回答我的问题的类似问题。
So my questionis: Could you tell me where the error in my code is or if there is another easier way to accomplish the same using a different way?
所以我的问题是：您能否告诉我代码中的错误在哪里，或者是否有另一种更简单的方法可以使用不同的方式完成相同的任务？
This is my first question here, so I hope everything is fine with the format of my code, the question asked and the information provided. Otherwise let me know.
这是我在这里的第一个问题，所以我希望我的代码格式、提出的问题和提供的信息都没有问题。否则让我知道。
回答by Siddharth Rout
5 Things...
5 件事...
1)You don't need this line
1）你不需要这条线
Set CurCell_1 = Range("B3") 'starting point in wb 1
Set CurCell_1 = Range("B3") 'starting point in wb 1
This line is pointless as you are setting it inside the loop
这条线毫无意义，因为您将其设置在循环内
2)You are setting this in a loop every time
2）您每次都将其设置为循环
Set CurCell_2 = Range("B4")
Set CurCell_2 = Range("B4")
Why would you be doing that? It will simply overwrite the values every time. Also which sheet is this range in??? (See Point 5)
你为什么要这样做？它每次都会简单地覆盖这些值。还有这个范围在哪个表？？？（见第 5 点）
3)CurCell_2is a Range and as JohnB pointed it out, it is not a method.
3)CurCell_2是一个范围，正如 JohnB 指出的那样，它不是一种方法。
Change
改变
Workbooks("My_WB_2").Worksheets(CStr(Group.Value)).CurCell_2.Value = Workbooks("My_WB_1").Worksheets("My_Sheet").CurCell_1.Value
Workbooks("My_WB_2").Worksheets(CStr(Group.Value)).CurCell_2.Value = Workbooks("My_WB_1").Worksheets("My_Sheet").CurCell_1.Value
to
到
CurCell_2.Value = CurCell_1.Value
CurCell_2.Value = CurCell_1.Value
4)You cannot assign range by just setting an "=" sign
4)不能仅通过设置“=”符号来分配范围
CurCell_2 = CurCell_2.Offset(1,0)
CurCell_2 = CurCell_2.Offset(1,0)
Change it to
将其更改为
Set CurCell_2 = CurCell_2.Offset(1,0)
Set CurCell_2 = CurCell_2.Offset(1,0)
5)Always specify full declarations when working with two or more objects so that there is less confusion. Your code can also be written as (UNTESTED)
5)在处理两个或多个对象时总是指定完整的声明，以减少混淆。您的代码也可以写为 ( UNTESTED)
Option Explicit
Sub trial()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim Group As Range, Mat As Range
Dim CurCell_1 As Range, CurCell_2 As Range
Application.ScreenUpdating = False
'~~> Change as applicable
Set wb1 = Workbooks("My_WB_1")
Set wb2 = Workbooks("My_WB_2")
Set ws1 = wb1.Sheets("My_Sheet")
Set ws2 = wb2.Sheets("Sheet2") '<~~ Change as required
For Each Group In ws1.Range("B4:P4")
'~~> Why this?
Set CurCell_2 = ws2.Range("B4")
For Each Mat In ws1.Range("A5:A29")
Set CurCell_1 = ws1.Cells(Mat.Row, Group.Column)
If Not IsEmpty(CurCell_1) Then
CurCell_2.Value = CurCell_1.Value
Set CurCell_2 = CurCell_2.Offset(1)
End If
Next
Next
Application.ScreenUpdating = True
End Sub
回答by JohnB
Workbooks("My_WB_2").Worksheets(CStr(Group.Value)).CurCell_2.Value
This will not work, since CurCell_2 is not a method of Worksheet, but a variable. Replace by
这行不通，因为 CurCell_2 不是 Worksheet 的方法，而是一个变量。替换为
Workbooks("My_WB_2").Worksheets(CStr(Group.Value)).Range("B4").Value
