基于其他单元格值复制/粘贴n次 [英] Copy/Paste n Times Based on Other Cell Values

问题描述

我遇到了这个问题。尽管在帖子上有这个 非常相似或这一个，我只是无法连接过滤单元格和根据使这项工作所需的公式结果进行复制之间的点。这是数据表 - 简化 - 我正在使用：

I've run in to a wall with this. Despite posts on SO like this one that is very similar or this one on Kioskea, I just cannot connect the dots in my head between filtering cells and copying based on a formula result that's needed to make this work. Here is the data table - simplified - I am working with:

     A    B   C        D     E      F    G    H
 R1 Name Num Status   #Orig #InPro #Act #Rem #RemStatus
 R2 ABC  032 Complete 22    0      11   11   Purged
 R3 LMN  035 In Prog  25    21     4    21   Pending Scan
 R4 XYZ  039 Not Act  16    16     0    16   Not Active

这描绘了纸质文件盒及其处置的状态：

This depicts the status of boxes of paper files and their disposition:

• D列是计划扫描的方框数


• E列是扫描的方框数


• F列是实际扫描的方框数

根据状态，G列和H列可以有三种含义：

Column G and H can have three meanings, based on status:

• 如果状态为未激活，则列G和H匹配，并且不需要执行任何操作


• 如果状态为正在进行，则假定列G中的数字是待处理的框数canning（只是原始减去实际）


• 如果状态为Complete，则假设G列中的数字是不需要扫描并且已清除的框数

我的代码（如下所示）应该迭代一个范围内的每一行（A2：H61）。如果状态为未激活，则可以忽略该行，并将其移至下一行。如果状态为正在进行或完成，则宏在其正在读取的任何行中都需要复制单元格A，B和H并将其粘贴（列）G次在另一个工作表中 - 在同一工作簿中 - 从下一个可用行开始。 深呼吸

What my code (shown below) is supposed to do is iterate through each row in a range (A2:H61). If the status is Not Active the row can be ignored and it moves on to the next row. If the status is In Progress or Complete, the macro, in whatever row it's 'reading', needs to copy cells A, B, and H and paste it (column)"G" number of times in another worksheet - within the same workbook - starting in the next available row. Deep breath

我知道。它也伤害了我的大脑。这是我到目前为止的代码：

I know. It hurts my brain, too. Here is the code I have so far:

Sub TEST_Copy_Process()

Dim srcrange As Range
Dim wb As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet

Set wb = ActiveWorkbook
Set ws1 = Worksheets("SIS Agregate")
Set ws2 = Worksheets("Center Detail")
Set srcrange = Range(wb.ws2.Cells("A2:H61"))


For Each Row In srcrange.Rows

If Row = "Not Active" And Row.Offset(0, 3) = SectorType Then
Continue
ElseIf Row = "In Progress" And Row.Offset(0, 3) = SectorType Then

ElseIf Row = "Complete" And Row.Offset(0, 3) = SectorType Then

End If

    Set LastCell = wb.ws1.Cells(wb.ws1.Rows.Count, "A").End(xlUp)
    LastCellRowNumber = LastCell.Row + 1

Next Row

End Sub

一旦我找到实际执行grunt工作的代码，我不知道哪个是最好的。如上所述，这个帖子帮帮我吧我正慢慢开始理解我在先生。 Excel中的。这个人似乎正在做If / Then工作，但我不明白它是如何复制或粘贴的。

Once I get to the code that is actually doing the grunt-work, I don't have the knowledge to sort out which is best. As noted above, posts like this have helpd get me here. And I am slowly starting to make sense of what I found on Mr. Excel. This person seems to be doing the If/Then work but I don't understand how it's copying or pasting.

我感谢任何和所有的帮助。即使你能指出一个有助于解释这个问题的资源（除了亚马逊上的书籍：），这将是一个很好的帮助！

I appreciate any and all help. Even if you can point me to a resource that will help explain this a bit (besides books on Amazon :] ) it would be a great help!

推荐答案

让我们看看这是否能让你走上正轨。你的代码看起来非常适合不太了解的人，所以也许你是一个快速学习的人：）

Let's see if this gets you on the right track. Your code looks very good for someone who doesn't know much, so maybe you are a quick study :)

我很困惑为什么你使用 .Offset（0,3）（在您的解释中似乎没有提及）以及为什么要与 SectorType 进行比较这是您提供的代码中的未定义变量。 我将假设这些是不必要的，并且无意中从其他示例中复制了（如果我弄错了，请告诉我。）

I am confused why you are using .Offset(0, 3) (which doesn't seem to be mentioned anywhere in your explanation) and also why you are comparing to SectorType which is an undefined variable in the code you have provided. I am going to assume these are unnecessary, and inadvertently copied from other examples (please let me know if I'm mistaken).

I尚未测试，但我会更改此作业：

I haven't tested it but I would change this assignment:

Set srcrange = Range(wb.ws2.Cells("A2:H61"))

如果没有其他原因，那就更直接了。我也将此范围更改为仅引用列H，因为这是您的逻辑居中的列轮（注意：我们总是可以使用 Offset <访问其他单元格/ code>和/或调整大小方法）。

to this, if for no other reason than it's a little more direct. I am also changing this range to only refer to column H, since that is the column round which your logic is centered (note: we can always access the other cells using Offset and/or Resize methods).

Set srcrange = wb.ws2.Range("H2:H61")

你逻辑的核心是在此块中，请注意删除 Row.Offset（9,3）= SectorType 。我还将使用 Select Case 而不是 If / Then 。当有多个或两个条件要测试时，我发现这些更容易阅读/理解：

The meat of your logic is in this block, note removal of Row.Offset(9, 3) = SectorType. I am also going to use a Select Case instead of If/Then. I find these easier to read/comprehend when there are more than one or two conditions to test:

For Each Row In srcrange.Cells  '## In this case, Cells/Rows is the same, but I use Cells as I find it less ambiguous
    Select Case Row.Value
        Case "Not Active"
        '## If the status is Not Active, Column G and H match it, and nothing needs to be done
            'Do nothing

        Case "In Progress", "Complete"
        '## If the status is In Progress or Complete, ... copy cells A, B, and H _
        '    and paste it (column)"G" number of times in another worksheet - _
        '    within the same workbook - starting in the next available row.

        '# Get the next empty cell in column A of the ws1
        '  I modified this to use Offset(1, 0), to return the cell BENEATH
        '  the last cell.
            Set LastCell = wb.ws1.Cells(wb.ws1.Rows.Count, "A").End(xlUp).Offset(1)

        '## copy the values from columns A, B, H to ws1
        '## Column A goes in column A
            LastCell.Value = Row.Offset(0, -7).Value 
        '## Column B goes in column B
            LastCell.Offset(0, 1).Value = Row.Offset(0, -6).Value 
        '## Column H goes in column C (because you did not specify)
            LastCell.Offset(0, 2).Value = Row.Value 
    End Select
Next Row

这篇关于基于其他单元格值复制/粘贴n次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！