Searching for 'Char(10)'

[Guest]

In VBA, I need to search a text string in a worksheet cell for the 'Return'
character - Char(10). I have tried using WorksheetFunction.Search (and
..Find), but I get the error 'Unable to get the Search property of the
WorksheetFunction class'. Any help on doing this kind of search?

 

[Frank Stone]

hi,
I just duplicate your actions on my machine and got the
same results. odd. must be the special character.
anyway i use flexFind and it found it.
so go to this site and down load flexfind
http://www.bmsltd.ie/MVP/
it is an adding. real neat tool
regards
Frank

 

[Guest]

I think you are right. I played with it some more, and the .Search works on
something other than a special character (or I wasn't specifying something
correctly when trying to use the special character). I'll get FlexFind.
Thanks.

hi,
I just duplicate your actions on my machine and got the
same results. odd. must be the special character.
anyway i use flexFind and it found it.
so go to this site and down load flexfind
http://www.bmsltd.ie/MVP/
it is an adding. real neat tool
regards
Frank

 

[Dave Peterson]

You use =char(10) in a worksheet formula.

You use chr(10) in VBA (or even vbLF):

Option Explicit
Sub testme()

Dim FoundCell As Range

With ActiveSheet.Cells
Set FoundCell = .Find(what:=Chr(10), _
after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "not found"
Else
MsgBox "Found at: " & FoundCell.Address(0, 0)
End If

End Sub

Worked ok for me.