Avatar of Murray Brown
Murray Brown
Flag 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
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Murray Brown

8/22/2022 - Mon
Ryan Chong

do you have the sample words?
Louis LIETAER

Also providing the excel will be helpful
Louis LIETAER

And maybe you want to find the last instance ?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Ejgil Hedegaard

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Tom Farrar

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
Saqib Husain

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

Murray Brown

ASKER
Thanks very much
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.