excel - 用于按笛卡尔积拆分行的 VBScript

Question

我有一个由另一个程序生成的 Excel 文件。我需要以这种方式拆分和提取 Excel 行：

来源：

“拆分”操作后的 Excel 应该是：

这样做的方法对我来说很清楚 - 但我没有设法在 VB 中编写它，逻辑应该是：我们有 3 列：名称、服务器、帐户 -> 结果将包含servers X accounts每个名称的笛卡尔积'。

int Count1=1 loop for counting the number of column B (servers-separated by comma)
int Count2=1 loop for counting the number of column C (accounts-separated by comma)

1 loop i=1 from i to count1(servers)

for each server
loop: j=1 from j to count2(account)
create a new row(column A:=Name, column B:=server[i],coulmn c:=account[j]) 

move to next row 

我只需要语法方面的帮助。

标签： excelvba

Answer 1

尝试，

Sub test()
    Dim vDB, vR(), vS(1 To 2), s
    Dim i As Long, j As Integer, n As Long
    Dim c1 As Integer, c2 As Integer

    vDB = Range("a1").CurrentRegion

    For i = 1 To UBound(vDB, 1)
        vS(1) = Split(vDB(i, 2), ",")
        vS(2) = Split(vDB(i, 3), ",")
        If InStr(vDB(i, 2), ",") Then
            c1 = 1
            c2 = 2
        Else
            c1 = 2
            c2 = 1
        End If

        For Each s In vS(c1)
            n = n + 1
            ReDim Preserve vR(1 To 3, 1 To n)
            vR(1, n) = vDB(i, 1)
            vR(c1 + 1, n) = s
            vR(c2 + 1, n) = vS(c2)(0)
        Next s
    Next i
    Sheets.Add
    Range("a1").Resize(n, 3) = WorksheetFunction.Transpose(vR)

End Sub