如何在列中查找文本并将行号保存在第一次找到的位置 - Excel VBA

How to find text in a column and saving the row number where it is first found - Excel VBA

提问by Kristina

I have the following column (column A) named project (rows column is just displaying the row number):

我有以下列（A 列）命名的项目（行列仅显示行号）：

rows    project
1       14
2       15
3       16
4       17
5       18
6       19
7       ProjTemp
8       ProjTemp
9       ProjTemp

I have an input message box where the user writes the new project name which I want inserted right after the last one. Ex: project 20 will be inserted right after project 19 and before the first "ProjTemp".

我有一个输入消息框，用户可以在其中写入我想在最后一个之后插入的新项目名称。例如：项目 20 将在项目 19 之后和第一个“ProjTemp”之前插入。

My theory was to locate the row number of the first "ProjTemp" and then insert a new row where the project is 20.

我的理论是找到第一个“ProjTemp”的行号，然后在项目为 20 的地方插入一个新行。

I was trying to use the Find function but I'm getting an overflow error (I'm sure I'm getting it because it's finding 3 "ProjTemp" strings and trying to set it to one parameter):

我试图使用 Find 函数，但出现溢出错误（我确定我得到了它，因为它正在查找 3 个“ProjTemp”字符串并尝试将其设置为一个参数）：

Dim FindRow as Range

with WB.Sheets("ECM Overview")
    Set FindRow = .Range("A:A").Find(What:="ProjTemp", _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        MatchCase:=False)
end with

How do I code this so I only find the row number of the fist "ProjTemp"? Is there a better way to do this, maybe a loop?

我该如何编码，以便我只能找到第一个“ProjTemp”的行号？有没有更好的方法来做到这一点，也许是一个循环？

Thanks, any help will be appreciated!

谢谢，任何帮助将不胜感激！

回答by mango

I'm not really familiar with all those parameters of the Findmethod; but upon shortening it, the following is working for me:

我不太熟悉该Find方法的所有参数；但在缩短它后，以下内容对我有用：

With WB.Sheets("ECM Overview")
    Set FindRow = .Range("A:A").Find(What:="ProjTemp", LookIn:=xlValues)
End With

And if you solely need the row number, you can use this after:

如果你只需要行号，你可以在之后使用它：

Dim FindRowNumber As Long
.....
FindRowNumber = FindRow.Row

回答by user2140261

Dim FindRow as Range

Set FindRow = Range("A:A").Find(What:="ProjTemp", _' This is what you are searching for
                   After:=.Cells(.Cells.Count), _ ' This is saying after the last cell in the_
                                                  ' column i.e. the first
                   LookIn:=xlValues, _ ' this says look in the values of the cell not the formula
                   LookAt:=xlWhole, _ ' This look s for EXACT ENTIRE MATCH
                   SearchOrder:=xlByRows, _ 'This look down the column row by row 
                                            'Larger Ranges with multiple columns can be set to 
                                            ' look column by column then down 
                   MatchCase:=False) ' this says that the search is not case sensitive

If Not FindRow  Is Nothing Then ' if findrow is something (Prevents Errors)
    FirstRow = FindRow.Row      ' set FirstRow to the first time a match is found
End If

If you would like to get addition ones you can use:

如果你想获得额外的，你可以使用：

Do Until FindRow Is Nothing
    Set FindRow = Range("A:A").FindNext(after:=FindRow)
    If FindRow.row = FirstRow Then
        Exit Do
    Else ' Do what you'd like with the additional rows here.

    End If
Loop

回答by j0chn

Alternatively you could use a loop, keep the row number (counter should be the row number) and stop the loop when you find the first "ProjTemp".
Then it should look something like this:

或者，您可以使用循环，保留行号（计数器应该是行号）并在找到第一个“ProjTemp”时停止循环。
然后它应该看起来像这样：

Sub find()
    Dim i As Integer
    Dim firstTime As Integer
    Dim bNotFound As Boolean

    i = 1
    bNotFound = True

      Do While bNotFound
        If Cells(i, 2).Value = "ProjTemp" Then
            firstTime = i
            bNotFound = false
        End If
        i = i + 1
    Loop
End Sub

回答by brettdj

A few comments:

几点意见：

1. Since the search position is important you should specify where you start the search. I use ws.[a1]and xlNextbelow so my search starts in A2of the specified sheet.
2. Some of Finds arguments - including lookatuse the prior search settings. So you should always specify xlWholeor xlPartto match all or part a string respectively.
3. You can do all you want - including inserting a row, and prompting the user for a new value (my code will suggest 20 if the prior value was 19) without using Selector Activate

1. 由于搜索位置很重要，因此您应该指定开始搜索的位置。我使用ws.[a1]和xlNext下面，所以我的搜索从A2指定的工作表开始。
2. 一些Finds 参数 - 包括lookat使用先前的搜索设置。因此，您应该始终指定xlWhole或xlPart分别匹配全部或部分字符串。
3. 你可以做你想做的一切——包括插入一行，并提示用户输入一个新值（如果先前的值是 19，我的代码将建议 20）而不使用Select或Activate

suggested code

建议代码

Sub FindEm()
Dim Wb As Workbook
Dim ws As Worksheet
Dim rng1 As Range
Set Wb = ThisWorkbook
Set ws = Wb.Sheets("ECM Overview")
Set rng1 = ws.Range("A:A").Find("ProjTemp", ws.[a1], xlValues, xlWhole, , xlNext)
If Not rng1 Is Nothing Then
rng1.EntireRow.Insert
rng1.Offset(-1, 0).Value = Application.InputBox("Please enter data", "User Data Entry", rng1.Offset(-2, 0) + 1, , , , , 1)
Else
MsgBox "ProjTemp not found", vbCritical
End If
End Sub

回答by sonny

Check for "projtemp" and then check if the previous one is a number entry (like 19,18..etc..) if that is so then get the row no of that proj temp ....

检查“projtemp”，然后检查前一个是否是数字条目（如 19,18..etc...），如果是，则获取该 proj temp 的行号 ....

and if that is not so ..then re-check that the previous entry is projtemp or a number entry ...

如果不是这样..然后重新检查前一个条目是 projtemp 还是一个数字条目......