Find exact text match
 

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

Find exact text match
 

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

"Graham" wrote in message
...
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

Find exact text match
 

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



"Graham" wrote in message
...
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

Find exact text match
 

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" wrote in message
...
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

Find exact text match
 

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

Graham wrote:
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