Finding Nth Occurrence via VBA or is there a quicker formula solution?

[greencroft]

I have successfully used a VBA solution as follows for this problem on numerous occasions, one that I recall I found on here quite some while ago - apologies that I have not recorded who to credit it to:

Function Nth_Occurrence(range_look As Range, find_it As String, occurrence As Long, offset_row As Long, offset_col As Long)
Dim lCount As Long
Dim rFound As Range
Set rFound = range_look.Cells(1, 1)
        For lCount = 1 To occurrence
            Set rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)
        Next lCount
        
Nth_Occurrence = rFound.Offset(offset_row, offset_col)
End Function

The reason for this question is that I am now trying to use it in a spreadsheet model where the dataset it is searching (range_look) is quite large - 4000 rows and I make 220 calls to the function from a sheet. This I think is markedly slowing the working of the model, especially as it uses an iterative process to solve something else which means multiple recalculations of the whole workbook.

I have considered several different options for speeding it up:

1. Investigating a formula solution to the Nth occurrence problem rather than a VBA one - hence the title to this question.
2. Via a macro, freezing the results of the function calls as values so the function is only called once and not each time the per iteration.
3. Simplifying where the function has to look by pulling the relevant bit of data from the 4000 row list and placing it as a much smaller list elsewhere such that the calls to the Nth occurrence function are then searching a materially smaller dataset.

I regret I can't share the whole model on this forum as it is commercially sensitive but would welcome comments on this aspect.

Many thanks

 

[Warship]

Maybe something from Chip:FindAll VBA Function
Looks like he returns an array, so I'm guessing you could index for your result.

Haven't used this specifically but Chip has wonderful code posted.

 

[greencroft]

Thanks Warship for that pointer. It looks a promising alternative.