在Excel VBA中，根据列数据创建新行 [英] In Excel VBA, create new rows based on column data

问题描述

对于该记录，我是一个未经培训的，仅记录宏的VBA用户。我尝试在这里和那里拿起一些碎片，但我仍然是一个完整的noob。请指出正确的方向！

For the record, I am an untrained, recorded-macro-only-VBA-user. I try to pick up bits and pieces here and there, but I'm still a total noob. Please point me in the right direction!

在每行上，部件号（列E）应与源和地址（列G和H）和描述（列I）。我说应该，但实际上，而不是每个部件号的一个源/地址组合，在许多文件中，一些行上最多有十五个不同的源/地址组合，源/地址组合在相邻的列表中列出列J / K，L / M，N / O等，将描述列推送到右侧。

On each row, part number (column E) should be associated with a source and address (column G and H) and description (column I). I say "should be", but in actuality, rather than one source/address combo for each part number, in many files there are up to fifteen different source/address combos on some lines, and the source/address combos are listed in adjacent columns J/K, L/M, N/O, etc., which pushes the description column over to the right.

我需要找到与源/地址组合一样重复行多个行的VB方法，并且除去每行除了一个组合外的所有内容。这里有一个例子：

I need to find a VB method for duplicating rows as many times as there are source/address combos, and stripping out all but one combo per row. Here's an example:

   A   B   C   D  Part#  F  Source1  Address1  Source2  Address2   Description
1  x   x   x   x  Part1  x  (S1)     (A1)                          Nut
2  x   x   x   x  Part2  x  (S1)     (A1)      (S2)     (A2)       Bolt

第2行有两个源/地址组合，需要在每行上只复制一个组合，如下所示：

Row 2 has two source/address combos and needs to be duplicated with only one combo on each row, like so:

   A   B   C   D  Part#  F  Source   Address  Description
1  x   x   x   x  Part1  x  (S1)     (A1)     Nut
2  x   x   x   x  Part2  x  (S1)     (A1)     Bolt
3  x   x   x   x  Part2  x  (S2)     (A2)     Bolt

在另一个文件中，我可能在任何给定的行上最多有十五个不同的源/这将需要重复十五次。

In another file I might have up to fifteen different source/address combos on any given row, which would then need to be duplicated fifteen times.

这是否有意义？在我的脑海中，我听到VBA功能，我从来没有像循环那样使用，do-while，do-until等等，但是我不知道足够的语法来开始实现任何东西。建议？

Is this making sense? In my head I'm hearing VBA functions I've never used like loop, do-while, do-until, etc. but I don't know enough syntax to begin implementing anything. Advice?

推荐答案

Sub Test()

Dim rw As Range, rwDest As Range, cellSrc As Range
Dim colDesc As Long, f As Range

    colDesc = 0
    'see if we can find the "description" column header
    Set f = Sheet1.Rows(1).Find(what:="Description", LookIn:=xlValues, lookat:=xlWhole)
    If Not f Is Nothing Then colDesc = f.Column

    Set rw = Sheet1.Rows(2)
    Do While Len(rw.Cells(, "E").Value) > 0
        Set cellSrc = rw.Cells(, "G")
        Do While Len(cellSrc.Value) > 0 And _
                 UCase(Sheet1.Rows(1).Cells(cellSrc.Column).Value) Like "*SOURCE*"
            Set rwDest = Sheet2.Cells(Rows.Count, "E").End(xlUp). _
                         Offset(1, 0).EntireRow
            rw.Cells(1).Resize(1, 6).Copy rwDest.Cells(1)
            cellSrc.Resize(1, 2).Copy rwDest.Cells(7)
            If colDesc > 0 Then rw.Cells(colDesc).Copy rwDest.Cells(9)

            Set cellSrc = cellSrc.Offset(0, 2)
        Loop
        Set rw = rw.Offset(1, 0)
    Loop

End Sub

这篇关于在Excel VBA中，根据列数据创建新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！