Last Row with a especific text

[irisqueiroz]

Hi, I'm trying to determine the last row that has a especific text.


I'm sisng the find function, and the code is:


Sub orgDebentures()


'Opções

Range("k6").Select

Do


On Error GoTo erro


Cells.Find(What:="Debentures", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _

xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

, SearchFormat:=False).Activate


ActiveCell = "Debentures"


myRow = ActiveCell.Row


Loop Until myRow = 1133


erro:


End Sub


To avoid infinite loop I wrote the statement DO LOOP UNTIL, but I don't know how to determine the last row that contains the word "debentures"... any idea how to solve it?


And other thing... how can I search for this word just in a specific column (K, in this case) and not in all cells of the sheet?


Thanks.

 

[SirJB7]

Hi, irisqueiroz!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Last%20Row%20with%20a%20especific%20text%20%28for%20irisqueiroz%20at%20chandoo.org%29.xlsm


In cells G1:H3 you have the parameters and returned values for your search.

The following code is used:

-----

[pre]

Option Explicit

' constants
Const gksWS = "Hoja1"

Sub GetLastDataOccurrenceCaller()
Dim I As Long
With Worksheets(gksWS)
GetLastDataOccurrence .Range("H1").Value, .Range("H2").Value, I
.Range("H3").Value = I
End With
End Sub

Sub GetLastDataOccurrence(psWhat As String, psWhere As String, plRow As Long)
' constants
' declarations
Dim rngSearch As Range, rngFound As Range
Dim I As Long
' start
Set rngSearch = Worksheets(gksWS).Range(psWhere)
' process
With rngSearch
Set rngFound = .Find(psWhat, After:=.Cells(.Rows.Count, 1), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlPrevious)
If Not rngFound Is Nothing Then
I = rngFound.Row
Set rngFound = .FindPrevious(rngFound)
If Not rngFound Is Nothing Then I = rngFound.Row
End If
End With
' end
Set rngFound = Nothing
Set rngSearch = Nothing
plRow = I
End Sub

[/pre]
-----


Just advise if any issue.


Regards!