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

Hi

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.

Thanks
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 ?
ASKER CERTIFIED SOLUTION
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Link to home
membership
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.


https://www.extendoffice.com/documents/excel/1052-excel-find-nth-occurrence.html#a1
This is the code in one line
cells.Find("computer",cells.Find("computer",cells.Find("computer",cells.Find("computer",,,xlpart),,xlpart),,xlpart),,xlpart).Row

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

ASKER

Thanks very much