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

excel, vba

Solution

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

Problem

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!

Original source