excel - 当范围包含特定文本时插入多行

Question

我正在尝试使用一个宏来遍历一列数据，并为它计算为“,”的每个实例插入一行，例如它会在 Joanne 上方插入另外 3 行

我目前在下面有这段代码，但它不起作用，我不确定我是否在正确的轨道上，因为我认为它正在寻找“，”只是单元格中的唯一内容？任何帮助/指导将不胜感激。

Sub InsertRow()
Dim cell As Range
For Each cell In Range("E2:E9999")
    If cell.Value = "," Then
        cell.EntireRow.Insert
    End If
Next cell
End Sub

Answer 1

插入多少行就有多少逗号

Option Explicit

Sub InsertCommaRows()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    Dim cString As String
    Dim CommasCount As Long
    Dim r As Long
    
    For r = lRow - 1 To 2 Step -1
        Debug.Print ws.Cells(r, "E").Address(0, 0)
        cString = CStr(ws.Cells(r, "E").Value)
        CommasCount = Len(cString) - Len(Replace(cString, ",", ""))
        If CommasCount > 0 Then
            ws.Cells(r + 1, "E").Resize(CommasCount).EntireRow _
                .Insert xlShiftDown, xlFormatFromLeftOrAbove
        End If
    Next r

    Application.ScreenUpdating = True

    MsgBox "Comma-rows inserted.", vbInformation

End Sub