Get Range and/or row of Nth Occurence? I need to adjust custom function

[chrisignm]

Hello,

I found a custom function that works perfectly to return the value of the Nth occurence of a string in a specified range:

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, 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

Now I would rather be interested in either the complete range of this Nth_occurence or just the row of this Nth_occurence.
The value I could get anyway by just adding the ".value" to the range.

I tried to replace "Nth_Occurrence = rFound.Offset(offset_row, offset_col)" with "Nth_Occurence = rFound.row", but it just gives me 0.

Could somebody please help me how to achieve this?

Thank you

 

[MickG]

Perhaps:-

Function nTh(rng As Range, str As String) As String
Dim R As Range
For Each R In rng
    If R.Value = str Then nTh = "Last " & str & " @ " & R.Address
Next R
End Function

 

[chrisignm]

Thanks, could you tell me how to specify the number of occurence like in the previous code? I tried to adapt your code, but failed.

Chris

 

[MickG]

Try:-

Function nTh(rng As Range, str As String) As String
Dim R As Range, c As Long, lst As String
For Each R In rng
    If R.Value = str Then
        c = c + 1
        lst = R.Address
    End If
Next R
    nTh = "Last " & str & " @ " & lst & ", " & "Count =" & c
End Function

 

[chrisignm]

I would actually like to specify for which occurence the range should be output when i call the function to use it for multiple purposes afterwards. Ill try with your code lateron. Thanks!

 

[chrisignm]

Hi again, I'm nearly done thanks to your code Just I don't know now, how I can actually make use of the range, how to get the value of it (and also do offset and get value of it).

That's what I got:

Function nTh(rng As Range, str As String, occurence As Long) As String
Dim R As Range, c As Long, lst As String
c = 0
For Each R In rng
    If R.Value = str Then
        c = c + 1
        lst = R.Address
    End If
If c = occurence Then Exit For
Next R


nTh = lst
End Function


Sub asd()
Dim bla As String
bla = nTh(Range("A:A"), "asd", 3)
Debug.Print bla
End Sub

The result of this looks like that: $A$5

How can I directly get the value of it? bla = nTh(Range("A:A"), "asd", 3).value won't work
Also ".row" won't work.

Thank you!

 

[chrisignm]

Found it. For anybody else searching it, the Address has to be put into a range to process it afterwards:

i.e.
bla = Range(nTh(Range("A:A"), "asd", 3)).Value
bla = Range(nTh(Range("A:A"), "asd", 3)).Row

Thanks for your help, Mick!