Find exact phrase does not work

Microsoft Excel 2002 (full product)
April 1, 2010 at 16:20:10
Specs: Vista, Office 2002
Hi there
I am trying to find an exact phrase in a column
but the .find function returns a cell value which does not match the complete word.

Here is my data in two different cells on the sheet:
/* X axis */

/* end X axis */

Here is my declaration:
...
S(14) = "/* X axis */"
...

Here is my formula which I use:
Set rng = .Find(What:=S(iCount), _
After:=.Cells(.Cells.Count),
_
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious, _
MatchCase:=True,
SearchFormat:=True)


The above function is true for both cases, is it
possible because of the /* before the string?

I have tried to use the search function box in
Excel but even if I tick the exact match and
whole word boxes it still find the longer string
as well?

Any suggestions please?

Cheers
C


See More: Find exact phrase does not work

Report •


#1
April 1, 2010 at 17:33:37
re: is it possible because of the /* before the string?

Well, I guess you could have tested that by removing one or both of those characters to see what happens.

Try this:

S(14) = "/~* X axis */"


Report •

#2
April 1, 2010 at 21:58:45
Hi DerbyDad
Before I posted here I had removed a character fro the search string to test and then
.Find could not find any string because of the xlWhole
parameter..;)

Thank you for the input, I will use another method to modify
the search string and then try again, I was really perplexed by
this problem and thought I will try my luck here again..;)

The file I am working on is almost 45000 rows high so it is
not an option to modify each string by hand..;)

Cheers
C


Report •

#3
April 2, 2010 at 05:01:55
Hi,

Did you try DerbyDad03's suggestion.

It works both in the worksheet find and in a VB find.

Regards


Report •

Related Solutions

#4
April 2, 2010 at 05:30:05
I'm not suggesting that you modify each string.

I'm suggesting that you modify the search parameter.

If you read the Excel (not VBA) Help file on Find you'll see that the * is a wildcard. As per the Help file, to search for a wildcard character, you need to place a tilde (~) before it in your search parameter.

With this layout, I ran FindTest1 and it exhibited the same problem you described, returning both A1 and A2.

       A     
1 /* X axis */
2 /* end X axis */ 

Sub FindTest1()
 For rw = 1 To 2
  With Range("A" & rw)
   Set c = .Find("/* X axis */", lookat:=xlWhole)
    If Not c Is Nothing Then MsgBox c.Address
  End With
 Next
End Sub

Then I added the tilde in FindTest2 and it only returned A1:

Sub FindTest2()
 For rw = 1 To 2
  With Range("A" & rw)
   Set c = .Find("/~* X axis */", lookat:=xlWhole)
    If Not c Is Nothing Then MsgBox c.Address
  End With
 Next
End Sub


Report •

#5
April 2, 2010 at 05:57:31
Thank you very much for the solution.
I did indeed search only in the VBA help file for solutions, will
remember next time to search in Excel help as well.
I will test later and report back.
Cheers
C

Report •


Ask Question