last occurence

[monika]

can it be possible to find the last occurence of a word,
rather than the 1st occurence in a particular column. I
hope i am clear

thanks
monika

 

[fabalicious]

Public Function LastOcc(Table As Range, Value As Variant)
' Finds the last occurence of a value in the first column

Dim i As Integer

For i = 1 To Table.Rows.Count

If Table.Cells(i, 1) = Value Then
LastOcc = i
End If

Next i

End Functio

 

[fabalicious]

Hi Monika.

This is modification of a function that finds you the k-th occurence of
a value. I didn't try it out so let me know in case it doesn't work,
alright?

Fabalicious

 

[Rob van Gelder]

How about this:

Sub test()
Dim rng As Range

With ActiveSheet.Columns(2)
Set rng = .Find("FindMe", .Cells(1), xlValues, xlPart, xlByColumns,
xlPrevious, False)
If Not rng Is Nothing Then rng.Select
End With
End Sub

 

[fabalicious]

Public Function LastOcc(Table As Range, Value As Variant, Col A
Integer)
' Finds the last occurence of a value in the first column

Dim i As Integer

For i = 1 To Table.Rows.Count

If Table.Cells(i, Col) = Value Then
LastOcc = i
End If

Next i

End Function


...almost the same as above but the function takes the column you wan
to search in as an argument (while before search could only b
performed in column 1). there's one restriction to this solution: th
cell containing the function should not be in the same column you ar
looking for occurences ... (will return '0' otherwise even if you
value does occur in this column)

Fa