Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Last Instance of "Text" in a column
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 -- |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Last Instance of "Text" in a column
Sorry for the double post. Not sure how????
-- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 -- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Last Instance of "Text" in a column
This worked. Thanks!
-- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Last Instance of "Text" in a column | Excel Discussion (Misc queries) | |||
How do I change the column heading in Excel to display "A" "B" "C | New Users to Excel | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Find a "date" in a column of dates in Excel 2000 | Excel Worksheet Functions | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) |