excel VBA -插入拆分单元格值

yizd12fk 于 2023-04-13 发布在其他

关注(0)|答案(1)|浏览(139)

如何同时分离两个范围？
我目前正在使用的（谢谢Vbasic）只拆分了其中一个：

With ThisWorkbook.Sheets("Cell Splitter")
    
Dim Descriptions() As String, dUpper As Long, d As Long
Dim r As Long, rString As String
    
For r = .Cells(.Rows.Count, "K").End(xlUp).Row To 3 Step -1
rString = CStr(.Cells(r, "K").Value)

If InStr(rString, ",") > 0 Then
Descriptions = Split(rString, ",")
dUpper = UBound(Descriptions)

For d = dUpper To 0 Step -1
.Cells(r, "K").Value = Descriptions(d)
.Rows(r).Copy
If d > 0 Then .Rows(r).Insert

Next d

End If

Next r

我尝试在代码中添加另一个范围，但得到了“400”错误。
例如：我添加了K:O或"K" & "O"（这不起作用）。我只想只处理2列。
我想让它做的是：
从这里：

对此：

先谢谢你。

excel

来源：https://stackoverflow.com/questions/75897635/vba-insert-spilt-cell-value

1条答案

按热度按时间

bkhjykvo1#

您发布的代码不会产生您想要的结果。正如我在评论中提到的，添加另一列并应用相同的代码将需要您处理行的插入。您可以尝试这种方法。

Dim r As Range, lr As Long, v, i As Integer

With Sheet1
  'this code will parse your text the same way as your initial code
  'but you can repeat this code and just replace the column, in this case K to O
  'it will yield the same result but as I've said it will not happen in the same time
  'but instead one after the other
  lr = .Range("K" & .Rows.Count).End(xlUp).Row
  Set r = .Range("K2:K" & lr)
  For i = lr To 2 Step -1
    v = Split(.Range("K" & i), ",")
    .Range("K" & i).Resize(UBound(v)).Insert xlDown, xlFormatFromLeftOrAbove
    .Range("K" & i).Resize(UBound(v) + 1) = Application.Transpose(v)
  Next

  ' repeat the exact code here but changing the column from K to whatever
End With

希望这能帮上忙。

赞(0）回复(0）举报 2023-04-13

我来回答

excel VBA -插入拆分单元格值

1条答案

相关问题

热门标签

最新问答