Excel VBA：循环通过单元格并将值复制到另一个工作簿 [英] Excel VBA: Loop through cells and copy values to another workbook

问题描述

我已经花了几个小时在这个问题上，但我没有成功找到一个工作的解决方案。

这是我的问题描述 ：

我想循环遍历一个工作簿中的一定范围的单元格，并将值复制到另一个工作簿。根据第一个工作簿中的当前列，我将值复制到第二个工作簿中的其他工作表中。
当我执行我的代码时，我总是得到运行时错误439：对象不支持此方法或属性。

我的代码看起来或多或少是这样的：

  Sub trial（）
 
 Dim Group As Range 
 Dim Mat As Range 
 Dim CurCell_1 As Range 
 Dim CurCell_2 As Range 
 
 Application.ScreenUpdating = False 
 
设置CurCell_1 =范围（B3）'起始点在wb 1 
 
工作簿中的每个组（My_WB_1）。工作表（My_Sheet）。范围（ B4：P4）
设置CurCell_2 = wb中的起始点2b $ b工作簿中的每个数据块（My_WB_1）。工作表（My_Sheet）。范围（A5 ：A29）
设置CurCell_1 =单元格（Mat.Row，Group.Column）'设置循环中的当前单元
如果不是IsEmpty（CurCell_1）
工作簿（My_WB_2）工作表（CStr（Group.Value））CurCell_2.Value =工作簿（My_WB_1）。工作表（My_Sheet）。CurCell_1.Value' rror'438对象不支持此方法或属性
 CurCell_2 = CurCell_2.Offset（1,0）'向下移动一个单元格
如果
下一个
下一个
 
 Application.ScreenUpdating = True 
 
 End Sub 
  

我已经进行了广泛的研究，我知道如何将值从一个工作簿复制到另一个工作簿，如果你使用明确的名称为您的对象（sheet&范围），但我不知道为什么它不工作，我使用变量实现它。
我还在stackoverlow和-obviously- Google上搜索，但是我没有找到类似的问题，可以回答我的问题。

所以我的问题是：
可以告诉我我的代码中的错误是哪里还是使用另一种方法来实现同样的方法？

这是我的第一个问题，所以我希望一切都符合我的代码格式，提出的问题和提供的信息。否则让我知道。

解决方案

5件事

1）您不需要此行

设置CurCell_1 = Range（ B3）'起始点wb 1

这条线是无意义的，因为你在循环中设置它

2）您每次都设置一个循环

Set CurCell_2 =范围（B4）

你为什么要这样做？它将简单地覆盖每次的值。这个范围在哪？ （见第5点）

3） CurCell_2 是一个范围，它不是一种方法。

更改

工作簿（My_WB_2 ）.Worksheets（CStr（Group.Value））。CurCell_2.Value =工作簿（My_WB_1）。工作表（My_Sheet）。CurCell_1.Value

到

CurCell_2.Value = CurCell_1.Value

4）您只能设置一个=符号来分配范围

CurCell_2 = CurCell_2.Offset（1,0）

将其更改为

设置CurCell_2 = CurCell_2.Offset（1,0）

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 
 
'~~> 
设置wb1 =工作簿（My_WB_1）
设置wb2 =工作簿（My_WB_2）
 
设置ws1 = wb1.Sheets（My_Sheet）
设置ws2 = wb2.Sheets（Sheet2）'< ~~根据需要更改
 
对于每个组在ws1.Range（B4：P4）
'~~ >为什么这个？ 
设置CurCell_2 = ws2.Range（B4）
对于每个Mat在ws1.Range（A5：A29）
设置CurCell_1 = ws1.Cells（Mat.Row，Group。列）
如果不是IsEmpty（CurCell_1）然后
 CurCell_2.Value = CurCell_1.Value 
设置CurCell_2 = CurCell_2.Offset（1）
结束如果
下一个
下一个
 
 Application.ScreenUpdating = True 
 End Sub 
  

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.

My code looks 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.

So my question is: 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.

解决方案

5 Things...

1) You don't need this line

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

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)

3)CurCell_2 is a Range and as JohnB pointed it out, it is not a method.

Change

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

4) You cannot assign range by just setting an "=" sign

CurCell_2 = CurCell_2.Offset(1,0)

Change it to

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)

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

这篇关于Excel VBA：循环通过单元格并将值复制到另一个工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！