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

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

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".

My theory was to locate the row number of the first "ProjTemp" and then insert a new row where the project is 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):

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?

Thanks, any help will be appreciated!

A few comments:

1. Since the search position is important you should specify where you start the search. I use ws.[a1] and xlNext below so my search starts in A2 of the specified sheet.
2. Some of Finds arguments - including lookat use the prior search settings. So you should always specify xlWhole or xlPart to 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 Select or 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

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:

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

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 ....

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

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