如何在Excel VBA中执行循环副本？ [英] How do I execute looping copies in Excel VBA?

问题描述

我是VBA的真正新手，我正在尝试创建一个复制/粘贴循环来自动执行任务。我一直在尝试这几天，到目前为止还没有成功，所以需要一点帮助！

所以基本原理如下。我有两个静态列表，一个包含6个字符串（static1），一个包含37个字符串（static2）。我需要这些以特定格式显示，以允许使用固定的导入规范导入另一个程序。我需要static1以6的块重复，我使用以下代码：

  Sheet3.Range（I1：I6 ）.Copy 
对于i = 0到totalChannels 
单元格（1 + 6 * i，2）。选择
 ActiveSheet.Paste 
  pre> 
 
 
我完全理解了这个工作原理，static1保存在单元格I1：I6中，它只是一次又一次地复制和粘贴，直到达到totalChannels指定的最大值。
 
 
 
 static2有点复杂。 static2包含在单元格G1：G37中我需要在一个循环中创建一个循环以复制单元格G1并将其粘贴到312个顺序行中，然后复制单元格G2，并将其粘贴到312个顺序行中，依此类推，直至到达G37。 
 
 
 
我一直在逐渐对vba进行自我教育，但这超出了我目前所在的位置，我在线找不到来源于我的解释一个简单的时尚 - 希望有人可以帮助!! 
 
 
 
我希望我已经彻底解释了一切，
 
 
 
谢谢。
解决方案
顺序行动比其他任何事都更有想象力。有很多方法可以做你想要的。
 
 
 
例如，如果要将 A1：A5 复制到 B1：B25 顺序，以下工作完全没有循环。
  Sub RapidFireOne（）
 
 Dim SrcRng As Range 
 Set SrcRng = Sheet1.Range（A1：A5）
 
'一次复制到B列。
 SrcRng.Copy Sheet1.Range（B1：B25）
 
 End Sub 
  
结果：
 
 
 
  
 
 
 
 
 
 
如果你真的想通过循环来做，使用步骤属性，如下所示：
 子
 
设置SrcRng = Sheet1.Range（A1：A5）
 
'重复复制到列B之间的一个空格。 
对于Iter = 1到25步骤5 
 SrcRng.Copy Sheet1.Range（B& Iter）
下一个
 
 End Sub 
  
结果与第一个相同。
 
 
 
 
 
 
如果要将每个单元格复制到批量范围，就像使用 static2 所做的一样，以下方法工作，只是修改它（基本上更改 5 到 317 或somesuch）：
  Sub RapidFireThree（）
 
'这一个顺序地复制每个单元格。 
 Dim SrcRng As Range 
 Set SrcRng = Sheet1.Range（A1：A5）
 
'将每个单元格复制到B列。
设置TargetRng = Sheet1.Range（B1：B5）
对于SrcRng 
中的每个单元格CellCopy TargetRng 
设置TargetRng = TargetRng.Offset（5，0）.Resize（5，1）
下一个
 
 End Sub 
  
结果：
 
 
 
  
 
 
 
 
 
 
让我们知道这是否有帮助。
 
I'm a real novice with VBA and I'm trying to create a copy/paste loop to automate a task at work.  I've been trying this for a few days and been unsuccessful so far, so need a little bit of help!

So the fundamentals are as follows. I've got two static lists , one containing 6 strings (static1) and one containing 37 strings (static2). I need these displayed in a specific format to allow an import into another program with a fixed import spec. I need static1 to repeat itself in blocks of 6 which I do with the following code:
Sheet3.Range("I1:I6").Copy
    For i = 0 To totalChannels
    Cells(1 + 6 * i, 2).Select
ActiveSheet.Paste
I understand fully how this works, static1 is held in cells I1:I6, and it's simply copied and pasted again and again until it reaches the maximum amount specified by totalChannels.

static2 is a little more complicated. static2 is contained in cells G1:G37 I need to create a loop within a loop to copy cell G1 and paste it into 312 sequential rows, then copy cell G2 and paste it into 312 sequential rows and so on, until it gets to G37.

I've been gradually educating myself on vba but this is ahead of where I'm currently at and I can't find a source online to explain this to me in a simple fashion - hoping someone on here can help!!

I hope I've explained everything thorough enough,

thanks.
解决方案
Sequential actions are more of an exercise in imagination than anything else. There are many ways to do what you want.

For example, if you want to copy A1:A5 to B1:B25 in sequence, the following works simply enough without a for-loop.
Sub RapidFireOne()

    Dim SrcRng As Range
    Set SrcRng = Sheet1.Range("A1:A5")

    'Copy in one go to Column B.
    SrcRng.Copy Sheet1.Range("B1:B25")

End Sub
Result:





If you really want to do it via loop and you want to do it across a sequence of ranges, use the Step property, like so:
Sub RapidFireTwo()

    Dim SrcRng As Range
    Set SrcRng = Sheet1.Range("A1:A5")

    'Copy repeatedly to Column B with one space between.
    For Iter = 1 To 25 Step 5
        SrcRng.Copy Sheet1.Range("B" & Iter)
    Next

End Sub
Result is same as the first one.



If you want to copy each cell to batch ranges like what you're doing with static2, the following method works, just modify it (basically changing 5 to 317 or somesuch):
Sub RapidFireThree()

    'This one copies each cell sequentially.
    Dim SrcRng As Range
    Set SrcRng = Sheet1.Range("A1:A5")

    'Copy each cell in order to Column B.
    Set TargetRng = Sheet1.Range("B1:B5")
    For Each Cell In SrcRng
        Cell.Copy TargetRng
        Set TargetRng = TargetRng.Offset(5, 0).Resize(5, 1)
    Next

End Sub
Result:





Let us know if this helps.

                        
这篇关于如何在Excel VBA中执行循环副本？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！