Find cells containing "Total"
 

Hello all,
How can I find all cells with the word "Total" included in the cell? For
example, one of the cells is "6/3/2006 Total", with "Total" always being the
last 5 characters. I've tried several things but nothing's worked. Can
anyone help? TIA

Find cells containing "Total"
 

Dim tCell as Range 'Cell Containing Total
Dim ftCell as Range 'Cell Containg First Total
Set ftCell = Cells.Find(What:="Total", After:=Range("A1"),
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
if ftCell is Nothing Then End Sub
Set tCell = ftCell
Do
'Your Code
Set tCell = Cells.Find(What:="Total", After:=tCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
Loop Until tCell.Address = ftCell.Address

HTH

Die_Another_Day
cottage6 wrote:
Hello all,
How can I find all cells with the word "Total" included in the cell? For
example, one of the cells is "6/3/2006 Total", with "Total" always being the
last 5 characters. I've tried several things but nothing's worked. Can
anyone help? TIA

Find cells containing "Total"
 

Give this a try...

Sub FindTotal()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String

Set wks = Sheets("Sheet1")
Set rngToSearch = wks.Cells
Set rngFound = rngToSearch.Find(What:="Total", _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Total was not found."
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.Select
End If

End Sub
--
HTH...

Jim Thomlinson


"cottage6" wrote:

Hello all,
How can I find all cells with the word "Total" included in the cell? For
example, one of the cells is "6/3/2006 Total", with "Total" always being the
last 5 characters. I've tried several things but nothing's worked. Can
anyone help? TIA

Find cells containing "Total"
 

Try this:

Dim myCell As Range
Dim firstCell As String

'set first search
Set myCell = Cells.Find(What:="Total")

If Not myCell Is Nothing Then
'do something
firstCell = myCell.Address
MsgBox myCell.Value
'continue searching
Do
Set myCell = Cells.FindNext(myCell)
If Not myCell Is Nothing And myCell.Address < firstCell Then
'do something again
MsgBox myCell.Value
End If
Loop While Not myCell Is Nothing And myCell.Address < firstCell

End If


--
Les Torchia-Wells


"cottage6" wrote:

Hello all,
How can I find all cells with the word "Total" included in the cell? For
example, one of the cells is "6/3/2006 Total", with "Total" always being the
last 5 characters. I've tried several things but nothing's worked. Can
anyone help? TIA