excel - 使用 VBA 从行中提取数据

Question

我有这个数据

我想像这样提取大小和颜色:

ID Color Size
1 Blue S
1 Blue M
1 Blue  L
1 Blue XL
1 Blue  XXL

我也可以有 2 个用逗号分隔的颜色值。我想要这样的结果:

ID Color Size
18 Rose S
18 Rose M
18 Rose L
18 Rose XL
18 Rose XXL
18 White S
18 White M
18 White L
18 White XL
18 White XXL

我在 VBA 中试过这个:

Sub MG11Sep42()
Dim Rng As Range, Dn As Range, Sp As Variant, Sp1 As Variant, Sp2 As Variant, Sp3 As Variant
Dim n As Long, Col As Variant, Sz As Variant, c As Long
Set Rng = Range("B1", Range("B" & Rows.Count).End(xlUp))
Cells(1, "D").Resize(, 3) = Array("ID", "Color", "Size")
c = 1
For Each Dn In Rng
    Sp = Split(Dn.Value, ":")
    Sp1 = Split(Sp(1), ";")
    Sp2 = Split(Sp(UBound(Sp)), ",")
    Sp3 = Split(Sp1(0), ",")
        For Each Sz In Sp2
            For Each Col In Sp3
                c = c + 1
                Cells(c, "D") = Dn.Offset(, -1).Value
                Cells(c, "E") = Sz
                Cells(c, "F") = Col
            Next Col
        Next Sz
Next Dn
End Sub

但错误是: 索引超出范围

Answer 1

看起来您需要跳过第一行。也许(未调试):

For Each Dn In Rng.offset(1,0).resize(rowsize:=rng.rows.count-1)
    Sp = Split(Dn.Value, ":")
    Sp1 = Split(Sp(1), ";")