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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

A few comments        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    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  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

User · Answer

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

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

Examples related to excel

Examples related to vba