Excel：分裂;将单元格值分隔为列，然后在连续的行中移位

2 回答

• 0

  干得好：

  Sub SplitColumnB()
      Dim r As Range
      Set r = [B2]
      Do While r.Value <> ""
          res = Split(r.Value, " ; ")
          i = 0
          For Each resStr In res
              If i > 0 Then r.Offset(1).EntireRow.Insert xlDown
              r.Offset(IIf(i > 0, 1, 0)).Value = resStr
              r.Offset(IIf(i > 0, 1, 0), -1).Value = Right(resStr, Len(resStr) - InStr(resStr, "@"))
              i = i + 1
          Next
          Set r = r.Offset(IIf(i > 0, i, 1))
      Loop
  End Sub
  

  回复于 2024-04-27T22:02:25+08:00
• 1

  尝试使用以下代码 . 将 Sheet1 的所有实例替换为工作表的名称 .

  Sub test()
  
      Dim Ref As String
      Dim Eid As String
      Dim RefR()
      Dim EidR()
  
      Rcnt = Sheets("Sheet1").Range("A65000").End(xlUp).Row
      K = 0
      L = 0
  
      For i = 2 To Rcnt
          Ref = Sheets("Sheet1").Range("A" & i).Value
          Temp = Split(Sheets("Sheet1").Range("B" & i).Value, ";")
          K = K + 1
          ReDim Preserve RefR(1 To K)
          RefR(K) = Ref
  
          For j = LBound(Temp) To UBound(Temp)
              If L <= UBound(Temp) Then
                  ReDim Preserve EidR(Rcnt, L)
                  L = UBound(Temp)
              End If
              EidR(K, j) = Temp(j)
          Next j
      Next i
  
      RowValue = 2
      For i = 1 To UBound(RefR)
          For j = 0 To L
  
              Sheets("Sheet1").Range("A" & RowValue).Value = RefR(i)
              Sheets("Sheet1").Range("B" & RowValue).Value = Trim(EidR(i, j))
              RowValue = RowValue + 1
          Next j
      Next i
  
  End Sub
  

  回复于 2024-04-27T22:02:25+08:00