Excel VBA - 逗号分隔单元格到行 [英] Excel VBA - Comma Delimited Cells to Rows

问题描述

寻找VBA代码将包含一列逗号分隔值的动态表转换为不带逗号分隔值的表。列有标题，命名范围可用于标识表和列。 给定数据中可能有这些值的任意数量的行。所以在这个例子中有4行数据，但实际上数据的范围可以是1到300行数据。

Looking for VBA code to convert a dynamic table which contains one column of comma separated values into a table with no comma separated values. Columns have titles, and named ranges can be used to identify the table and columns. There could be any number of rows of these values in "Given Data". So in this example there are 4 rows of data, but in practice the data can range from 1 to over 300 rows of data.

给定数据（Sheet1）：

Given data ("Sheet1"):

A                   B       C      D
CPN:                MPN:    Price: Text:
CPN1, CPN2, CPN3    MPN1    1.25   Example1
CPN4, CPN6          MPN5    3.50   Example2
CPN7                MPN4    4.20   Example3
CPN8, CPN9          MPN2    2.34   Example4

我需要的结果是另一张表上的表格，只需说Sheet2，每行逗号分隔值为A的行与原始表单中的相应数据不删除第一个数据

The result I need is a table on another sheet, lets just say "Sheet2", with rows for each comma separated value in "A" with the corresponding data from the original sheet without deleting the data from the first sheet.

需要的结果（Sheet2）：

Needed Result ("Sheet2"):

A     B     C      D
CPN:  MPN:  Price: Text:
CPN1  MPN1  1.25   Example1
CPN2  MPN1  1.25   Example1
CPN3  MPN1  1.25   Example1
CPN4  MPN5  3.50   Example2
CPN6  MPN5  3.50   Example2
CPN7  MPN4  4.20   Example3
CPN8  MPN2  2.34   Example4
CPN9  MPN2  2.34   Example4

我已尝试从此处修改以下代码，但是不能让它来处理我的价值类型。任何帮助将不胜感激。

I have tried modifying the code below from Here but was not able to get it to handle my value types. Any help would be greatly appreciated.

Private Type data
   col1 As Integer
   col2 As String
   col3 As String
End Type

Sub SplitAndCopy()

   Dim x%, y%, c%
   Dim arrData() As data
   Dim splitCol() As String

   ReDim arrData(1 To Cells(1, 1).End(xlDown))

   x = 1: y = 1: c = 1

   Do Until Cells(x, 1) = ""
       arrData(x).col1 = Cells(x, 1)
       arrData(x).col2 = Cells(x, 2)
       arrData(x).col3 = Cells(x, 3)

       x = x + 1
    Loop

    [a:d].Clear

    For x = 1 To UBound(arrData)

        Cells(c, 2) = arrData(x).col2
        splitCol = Split(Mid(arrData(x).col3, 2, Len(arrData(x).col3) - 2), ",")

        ' sort splitCol

        For y = 0 To UBound(splitCol)
            Cells(c, 1) = arrData(x).col1
            Cells(c, 3) = splitCol(y)
            c = c + 1
        Next y

    Next x

End Sub

推荐答案

Public Sub textToColumns()

Set ARange = Range("A:A")
Set BRange = Range("B:B")
Set CRange = Range("C:C")
Set DRange = Range("D:D")

Dim arr() As String

lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Set out = Worksheets.Add
out.Name = "out"
outRow = 2

For i = 2 To lr
    arr = Split(ARange(i), ",")
    For j = 0 To UBound(arr)
        out.Cells(outRow, 1) = Trim(arr(j))
        out.Cells(outRow, 2) = BRange(i)
        out.Cells(outRow, 3) = CRange(i)
        out.Cells(outRow, 4) = DRange(i)
        outRow = outRow + 1
    Next j
Next i

End Sub

我没有做标题或正确处理输出表，但您可以基本看到发生了什么。

I didn't do the headers or deal properly with the output sheet but you can see basically what's going on.

这篇关于Excel VBA - 逗号分隔单元格到行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！