vba - 根据单元格内容和识别主值复制行

Question

我正在寻找这个操作: How do I duplicate rows based on cell contents (cell contains semi-colon seperated data)

但添加了一列: Starting table vs End result

我有:

| Name   | Size       | Photo   |
|--------|------------|---------|
| Tshirt | 10, 12, 14 | 144.jpg |
| Jeans  | 30, 40, 42 | 209.jpg |
| Dress  | 8          | 584.jpg |
| Shoe   | 6          | 178.jpg |

我想要什么:

| Name   | Size | Photo   | Primary |
|--------|------|---------|---------|
| Tshirt | 10   | 144.jpg | 1       |
| Tshirt | 12   | 144.jpg | 0       |
| Tshirt | 14   | 144.jpg | 0       |
| Jeans  | 30   | 209.jpg | 1       |
| Jeans  | 40   | 209.jpg | 0       |
| Jeans  | 42   | 209.jpg | 0       |
| Dress  | 8    | 584.jpg | 1       |
| Shoe   | 6    | 178.jpg | 1       |

现在我发现的代码运行完美，但我不知道如何添加“Primary”列。

Sub SplitCell()
Dim cArray As Variant
Dim cValue As String
Dim rowIndex As Integer, strIndex As Integer, destRow As Integer
Dim targetColumn As Integer
Dim lastRow As Long, lastCol As Long
Dim srcSheet As Worksheet, destSheet As Worksheet

targetColumn = 2 'column with semi-colon separated data

Set srcSheet = ThisWorkbook.Worksheets("Sheet1") 'sheet with data
Set destSheet = ThisWorkbook.Worksheets("Sheet2") 'sheet where result will be displayed

destRow = 0
With srcSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    For rowIndex = 1 To lastRow
        cValue = .Cells(rowIndex, targetColumn).Value 'getting the cell with semi-colon separated data
        cArray = Split(cValue, ";") 'splitting semi-colon separated data in an array
        For strIndex = 0 To UBound(cArray)
            destRow = destRow + 1
            destSheet.Cells(destRow, 1) = .Cells(rowIndex, 1)
            destSheet.Cells(destRow, 2) = Trim(cArray(strIndex))
            destSheet.Cells(destRow, 3) = .Cells(rowIndex, 3)
        Next strIndex
    Next rowIndex
End With
End Sub

感谢您的帮助!

Answer 1

尝试对代码进行轻微修改，您必须声明附加变量DimpriorityAsBoolean:

For rowIndex = 1 To lastRow
    cValue = .Cells(rowIndex, targetColumn).Value 'getting the cell with semi-colon separated data
    cArray = Split(cValue, ";") 'splitting semi-colon separated data in an array
    priority = True
    For strIndex = 0 To UBound(cArray)
        destRow = destRow + 1
        destSheet.Cells(destRow, 1) = .Cells(rowIndex, 1)
        destSheet.Cells(destRow, 2) = Trim(cArray(strIndex))
        destSheet.Cells(destRow, 3) = .Cells(rowIndex, 3)
        destSheet.Cells(destRow, 4) = IIf(priority, 1, 0)
        priority = False
    Next strIndex
Next rowIndex