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

Question

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!

Answer 1

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

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