在特定文本下方插入行(文本出现次数)vba-excel
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/25032669/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert row below a specific text (text occurrence times) vba-excel
提问by ghostlegend
This code searches all "-" in column A and when it finds that character in a cell, adds a new row below:
此代码搜索 A 列中的所有“-”,当它在单元格中找到该字符时,在下面添加一个新行:
Sub insertRow()
Dim c As Range
For Each c In Range("A:A")
If c.Value Like "*-*" Then
c.Offset(1, 0).EntireRow.Insert
End If
Next c
End Sub
I want to insert a row below every "-". If there are three "-" in a cell, I want to add three rows below that row and put in the text after every "-". How can I do that?
我想在每个“-”下方插入一行。如果一个单元格中有三个“-”,我想在该行下方添加三行,并在每个“-”之后放入文本。我怎样才能做到这一点?
e.g.:
column A:
str1-str2-str3-str4
str5
str6-str7
str8-str9-str10
str11-
例如:
A列:
str1-str2-str3-str4
str5
str6-str7
str8-str9-str10
str11-
and the result like this:
column A:
str1
str2
str3
str4
str5
str6
str7
str8
str9
str10
str11
结果如下:
A列:
str1
str2
str3
str4
str5
str6
str7
str8
str9
str10
str11
Can anyone help me? Thanks!
谁能帮我?谢谢!
回答by IAmDranged
Something like the below should work - it relies on the array base 0 default indexing. If execution time is a concern, there may be ways to optimize though
像下面这样的东西应该可以工作 - 它依赖于数组 base 0 默认索引。如果执行时间是一个问题,则可能有优化的方法
Sub t_exe566965()
Dim r As Range
Dim a As Variant
For Each r In Range("A:A")
If r.Value Like "*-*" Then
Debug.Print True
a = Split(r.Value, "-")
r.Offset(1, 0).Resize(UBound(a)).EntireRow.Insert Shift:=xlDown
For i = LBound(a) To UBound(a)
r.Offset(i, 0).Value = a(i)
Next i
End If
Next r
End Sub