Find exact text match

[Graham]

I am having a problem with the part of a procedure below in that I want it to find an
exact match and it does not do that. For example Cells(n,14) at any point may have SB and
the loop is picking up NF-TSB in Rng. I would value any guidance.

Set Rng = Range("AA1:AA30")
With Rng
For N = 15 To 42
Set C = .Find(Cells(N, 14).Value, LookIn:=xlValues)
If Not C Is Nothing Then
firstAddress = c.Address
Do
----STATEMENTS
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
Next
End With

Kind Regards,
Graham

 

[kounoike]

How about this one

Dim k As Variant
Set Rng = Range("AA1:AA30")
For N = 15 To 42
k = Application.Match(Cells(N, 14), Rng, 0)
If Not IsError(k) Then
'----STATEMENTS
End If
Next

keiji

 

[Peter T]

Always best to include the Find function's optional arguments, which can be
left in an unknown state by previous manual use of Find.

change

Set C = .Find(Cells(N, 14).Value, LookIn:=xlValues)

to
Set c = .Find(Cells(N, 2).Value, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

I want it to find an exact match

use LookAt:=xlWhole

If you want case sensitive change MatchCase:=False to MatchCase:=True

The example is suitable for use in XL 97 & 2000, it's probably OK to omit
other arguments introduced in later versions.

Regards,
Peter T

 

[Mark Ivey]

See if this works right...

Set Rng = Range("AA1:AA30")
With Rng
For N = 15 To 42
Set c = .Find(Cells(N, 14).Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
'----STATEMENTS
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
Next
End With



Mark

 

[Graham]

Thanks to all for the replies, I have now managed to get the procedure to work fine thanks
to your efforts. I am very grateful for all your help.

Graham