在特定文本下方插入行(文本出现次数)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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-12 04:01:46  来源:igfitidea点击:

Insert row below a specific text (text occurrence times) vba-excel

excelvbaexcel-vbainsertrows

提问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