I need to find the last occurrence of the word "Total" in a column. I can
find the last used cell in the column but failed when I tried to start the
search up from that point to find Total. Can anyone help? TIA.

Something like this?...

Sub test()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Columns("A")


Set rngFound = rngToSearch.Find(What:="Total", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If Not rngFound Is Nothing Then rngFound.Select
End Sub
--
HTH...

Jim Thomlinson


"cottage6" wrote:

I need to find the last occurrence of the word "Total" in a column. I can
find the last used cell in the column but failed when I tried to start the
search up from that point to find Total. Can anyone help? TIA.

First select the column and then run:

Sub mac()
Dim s As String
Dim r As Range
Dim l As Long
s = "Total"
l = 0
For Each r In Selection
If InStr(r, s) Then
l = r.Row
End If
Next
MsgBox l
End Sub
--
Gary''s Student


"cottage6" wrote:

I need to find the last occurrence of the word "Total" in a column. I can
find the last used cell in the column but failed when I tried to start the
search up from that point to find Total. Can anyone help? TIA.

try this ARRAY formula
=MAX(IF(D1:D100="Total",ROW(D1:D100)))

or
Sub findlasttotal()
For I = 20 To 1 Step -1
If UCase(Cells(I, "d")) = "TOTAL" Then
MsgBox Cells(I, "d").Row: Exit Sub
End If
Next
End Sub

--
Don Guillett
SalesAid Software

"cottage6" wrote in message
...
I need to find the last occurrence of the word "Total" in a column. I can
find the last used cell in the column but failed when I tried to start the
search up from that point to find Total. Can anyone help? TIA.