Link to home
Create AccountLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Excel VBA find the 4th instance of a word


What Excel VBA code would use to find the 4th instance of the word "CLOSING" using the FIND function and return the row address?
The instance changes so I need to sometimes find the 5th or 6th instance.

Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

do you have the sample words?
Also providing the excel will be helpful
And maybe you want to find the last instance ?
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Here is the Excel version:

Find nth occurrence (position) of a character in a Cell with Find formula
In a blank cell, enter the formula =FIND("c",A1,FIND("c",A1)+2).
And then press the Enter key. ...
Note: You can change the 2 in the formula based on your needs.
This is the code in one line

Open in new window

You can make it more readable like this 

Cells.Find("computer", _
    Cells.Find("computer", _
        Cells.Find("computer", _
            Cells.Find("computer", , , xlPart) _
        , , xlPart) _
    , , xlPart) _
, , xlPart).Row

Open in new window

Avatar of Murray Brown


Thanks very much