如何重复另一列中每行的所有列值 [英] How to repeat all column values for each row in another column

问题描述

我有一张表格有两列：

Height Width
400    200
500    300
600    400
700
800
...

并且需要在其旁边创建一个第二个表来重复宽度列中每个值的整个列高度，同时复制，以便基本上获得所有可能的组合：

And need to create a second table next to it to repeat entire column height for each value in the width column, whilst copying, to basically get every possible combination:

Height Width
400    200
500    200
600    200
700    200
800    200
400    300
500    300
600    300
700    300
800    300

推荐答案

我可以想到运行嵌套循环的最简单的方法。您可能需要修改下面的代码，具体取决于工作表的结构，但是它应该让你走。

The simplest way I could think of running nested loops. You might need to modify the code below depending on the structure of your sheets, but it should get you going.

Sub loops()

Dim n_height, n_width, c As Integer

With ThisWorkbook.Sheets("Sheet1")

n_height = .Cells(Rows.Count, 1).End(xlUp).Row 'Assuming height is in column A
n_width = .Cells(Rows.Count, 2).End(xlUp).Row 'Assuming width is in column B

c = 2

For i = 2 To n_height
    For j = 2 To n_width
        .Range("D" & c).Value = .Range("A" & i).Value 'Prints heights in column D
        .Range("E" & c).Value = .Range("B" & j).Value 'Prints widths in column E
        c = c + 1
    Next j
Next i

End With

End Sub

这篇关于如何重复另一列中每行的所有列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！