Find Last Instance of "Text" in a column
Sub FindThemThings()
'Jim Cone - Portland, Oregon - January 2009
Dim rng As Range
Dim rCell As Range
Dim rFound As Range
Set rFound = Range("A1") 'so it is not nothing
Set rng = Range("C1", Cells(Rows.Count, 3).End(xlUp))
For Each rCell In rng.Cells
If Len(rCell.Formula) Then 'no blanks
If Asc(rCell) = 42 Then '* is the 1st character
Set rFound = Application.Union(rFound, rCell)
End If
End If
Next
MsgBox Mid$(rFound.Address, 6) 'remove A1 address
Set rFound = Nothing
Set rCell = Nothing
Set rng = Nothing
End Sub
--
Jim Cone
Portland, Oregon USA
"Chris Premo"
wrote in message
I have a sorted column of text that I want to find the last instance
where the text begins with an asterisk ("*"). I can use this code to
do the search, but I would have to know how many times to "continue the
search". Also, the "~*" will also find any word with the asterisk in
it, while I only want those words with the asterisk as the first
character.
Columns("C:C").Select
Selection.Find(What:="~*", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Selection.FindNext(After:=ActiveCell).Activate
--
|