Cells.Find wont work with "-"
 

Hello,

I have a confusing problem with some VBA I have written for MS Excel.

I am using the find function as shown below

Cells.Find(What:="a-", After:=ActiveCell, LookIn:=xlValues, LookAt
_
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate

This works perfectly with

What:="a"

but not when i add the "-".

I have had this working with "a-" on another computer also which makes
it even more confusing.

Might I be missing a VBA reference library, there are so many I dont
know how to tell which I might be missing.

OS: Windows NT
Excel: 2000

thanks in advance...

A

Cells.Find wont work with "-"
 

Works fine for me with XL2000, XP.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

wrote in message
ps.com...
Hello,

I have a confusing problem with some VBA I have written for MS Excel.

I am using the find function as shown below

Cells.Find(What:="a-", After:=ActiveCell, LookIn:=xlValues, LookAt
_
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate

This works perfectly with

What:="a"

but not when i add the "-".

I have had this working with "a-" on another computer also which makes
it even more confusing.

Might I be missing a VBA reference library, there are so many I dont
know how to tell which I might be missing.

OS: Windows NT
Excel: 2000

thanks in advance...

A

Cells.Find wont work with "-"
 

Sub abc()
Dim rng As Range
Dim rng1 As Range
Dim sAddr As String
Set rng = Cells.Find(What:="a-", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
sAddr = rng.Address
Do
If Not rng1 Is Nothing Then
Set rng1 = Union(rng1, rng)
Else
Set rng1 = rng
End If
Set rng = Cells.FindNext(rng)
Loop While rng.Address < sAddr
rng1.Select
msgbox "found at " & rng1.Address

Else
MsgBox "Not found"
End If
End Sub

worked fine for me.
It even found cells where the "-" was produced by formatting.

--
Regards,
Tom Ogilvy


" wrote:

Hello,

I have a confusing problem with some VBA I have written for MS Excel.

I am using the find function as shown below

Cells.Find(What:="a-", After:=ActiveCell, LookIn:=xlValues, LookAt
_
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate

This works perfectly with

What:="a"

but not when i add the "-".

I have had this working with "a-" on another computer also which makes
it even more confusing.

Might I be missing a VBA reference library, there are so many I dont
know how to tell which I might be missing.

OS: Windows NT
Excel: 2000

thanks in advance...

A