excel - 如何将循环中的某些范围复制到正确的方向？

Question

我尝试将一些范围(表格)复制到正确的方向，但我有一个问题，因为正确的方向是字符。我的函数获取表中的副本数量和行数(表范围是动态的)。

Function DrawBorder(Rows As Long, Amount As Long)

    Dim rng As Range
    Dim WS As Worksheet
    Dim firstRow As Long
    Dim firstCol As Long
    Dim lastRow As Long
    Dim lastCol As Long

    Let firstRow = 2
    Let firstCol = 2
    Let lastRow = Rows + 2
    Let lastCol = 4

    Set WS = Sheets("Sheet1")
    Set rng = WS.Range("B" & firstRow & ":" & "D" & lastRow)

    'Borders of the cells inside the range
    rng.Borders.LineStyle = xlContinuous

    'Border of the range as a whole with double lines
    rng.Borders(xlEdgeTop).LineStyle = xlContinuous
    rng.Borders(xlEdgeTop).Weight = xlThick
    rng.Borders(xlEdgeBottom).LineStyle = xlContinuous
    rng.Borders(xlEdgeBottom).Weight = xlThick
    rng.Borders(xlEdgeLeft).LineStyle = xlContinuous
    rng.Borders(xlEdgeLeft).Weight = xlThick
    rng.Borders(xlEdgeRight).LineStyle = xlContinuous
    rng.Borders(xlEdgeRight).Weight = xlThick

   ' Paste to multiple destinations
   rng.Copy Destination:=Sheet1.Range("F" & firstRow & ":" & "H" & lastRow)
   rng.Copy Destination:=Sheet1.Range("J" & firstRow & ":" & "L" & lastRow)
   rng.Copy Destination:=Sheet1.Range("N" & firstRow & ":" & "P" & lastRow)
   rng.Copy Destination:=Sheet1.Range("R" & firstRow & ":" & "T" & lastRow)

End Function

我想循环执行此操作，但我不知道如何增加列的目标。

这就是我需要的:



我使用的最终循环:

Dim i As Long
For i = 0 To Amount - 1 'copy "Amount" times
    rng.Copy Destination:=rng.Offset(ColumnOffset:=4 * i)
Next i

谢谢大家!

Answer 1

将循环与 Range.Offset property 结合使用“移动”/偏移您的范围。

举一个例子:

Dim i As Long
For i = 1 to Amount 'copy "Amount" times
    'your code here

    rng.Copy Destination:=Sheet1.Range("F" & firstRow & ":" & "H" & lastRow).Offset(ColumnOffset:=4 * i))
Next i