如何将2个单独的数组合并到Excel中的广泛合并列表

问题描述：

我一直在到处寻找它，尽管似乎是一个经常性的问题，但我似乎找不到答案.

I've been looking for it everywhere but I can't seem to find the answer although it seems an regular problem.

我正在尝试自动在excel中复制单元格. 我有两个列表:列表1的值为1,2,3,4，第二个列表的值为a，b，c，d. 现在，我想要一个文件，其中列表1中的每个值都在excel中重复了列表2中的值.所以:

I'm trying to automate the duplication of cells in excel. I have two lists: list 1 with values 1,2,3,4 and the second list is with values a,b,c,d. Now I want to have a file where for every value in list 1, the values in list two are duplicated in excel. So:

1 - a
1 - b
1 - c
1 - d
2 - a
2 - b
2 - c
2 - d
3 - a
...

我想知道excel中是否有一个函数，或者是否没有可以用来解决此问题的宏?对于这个简短的列表，使用自动填充当然很容易，但是当列表包含数百个值时，它将变得更加复杂...

I'm wondering if there's a function within excel or if not a macro that I could use to solve this? For this short list it is of course easy to do with autofill, but when the list consists of a few hundred values, it gets more complicated...

答

这应该很容易理解...

This should be really easy to understand ...

打开VBE ALT + F11 ，然后右键单击VBA项目.插入一个模块，然后复制粘贴以下代码.点击 F5 运行

Open VBE ALT+F11 and right click VBA project. Insert a Module and copy-paste the below code. Hit F5 to run

Sub Main()

    Dim number As Range
    Dim letter As Range

    For Each number In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        For Each letter In Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
            With Sheet2
                .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row + 1) = number
                .Range("B" & .Range("b" & Rows.Count).End(xlUp).Row + 1) = letter
            End With
        Next
    Next

End Sub

上面的代码假定您的sheet1看起来像这样

The above code assumes that your sheet1 looks like this

，结果将像这样在Sheet2上显示

and the results will be on Sheet2 like this

如何将2个单独的数组合并到Excel中的广泛合并列表

相关推荐