如何在vba中包含特定的字符串，如何循环使用单元格并创建新行？ [英] How do I loop through cells and create a new row if it contains a specific string in vba?

问题描述

我想通过内容循环遍历列A中的所有单元格，如果它包含特定的字符串，则在单元格上方创建一个新行。这是我到目前为止的代码：

I want to loop through all the cells in column A with content and create a new row above a cell if it contains a specific string. Here is the code I have so far:

 Dim rng_cell As Range

    For Each rng_cell In Range(Range("A1"), Range("A1").End(xlDown))

        If rng_cell.Value = "string" Then
            rng_cell.EntireRow.Insert xlUp
        End If

    Next rng_cell

任何想法为什么不起作用如何解决它？

Any idea why this doesn't work and how to fix it?

推荐答案

让我们说你的Excel表包含以下内容：

Let's say your Excel sheet contained the following:

   A
1  hello
2  moon
3  rich
4  dells
5  cat

您的宏无法正常工作的原因是因为您正在成功创建一个新行，然后立即该宏被放弃到下一行，找到相同的匹配，并添加一个新的空行，并转到下一行，并找到相同的匹配...以及on和on。

The reason your macro wasn't working as desired is because you were successfully creating a new line and then immediately the macro was dropping to the next line and finding the same match, and adding a new empty line, and going to the next line and finding the same match...and on and on.

如果我们想输入一行以上的新行code> rich ，这样的宏可能会更好：

If we wanted to enter a new line above the line with rich, a macro like this might work better:

Sub macro1()
    Range("A1").Select

    ' remember the last row that contains a value
    Dim LastRow As Integer
    Dim CurrentRow As Integer
    LastRow = Range("A1").End(xlDown).Row
    CurrentRow = 1

    ' keep on incrementing the current row until we are
    ' past the last row
    Do While CurrentRow <= LastRow

        ' if the desired string is found, insert a row above
        ' the current row
        ' That also means, our last row is going to be one more
        ' row down
        ' And that also means, we must double-increment our current
        ' row
        If Range("A" & CurrentRow).Value = "rich" Then
            Range("A" & CurrentRow).EntireRow.Insert xlUp
            LastRow = LastRow + 1
            CurrentRow = CurrentRow + 1
        End If

        ' put the pointer to the next row we want to evaluate
        CurrentRow = CurrentRow + 1

    Loop

End Sub

运行后，输出将如下所示：

After running this, the output will look like this:

   A
1  hello
2  moon
3
4  rich
5  dells
6  cat

尝试看看它是如何适用于您的。随意调整您的方案。

Give it a try and see how it works for you. Feel free to tweak it for your scenario.

这篇关于如何在vba中包含特定的字符串，如何循环使用单元格并创建新行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！