Finding Values Too Slow

[Henry Stockbridge]

Hello,

I am running the following code, and it runs extremely slow. Here is
a sample of the code and an the end result of what I am trying to
accomplish:

Sub FindMatchedNames()

For Each s In Worksheets(1).Range("c2:c5").Cells 'Real range is
c2:c598
For Each t In Worksheets(1).Range("b2:b5").Cells 'Real range is
b2:b10000
If Worksheets(1).Range("b2:b5").Find(s.Value) Is Nothing Then
'If a match is not found, no
s.Offset(0, 1).Formula = "No"
Else
' If a match is found, yes
s.Offset(0, 1).Formula = "Yes"
End If
Next
Next

End Sub

'''''''''''''''''''''
Sample
'''''''''''''''''''''
a b c
1 Name1 Name2 Correct
2 <-Bill Smith-> Jan Cooper No
3 (Jane Cooper) Ted Hill Yes
4 <<Sally Jones>> Bill Smit Yes
5 -ted hill- Sally Jones Yes
n ValueHere ValueThere ...

Thanks in advance,

Henry

 

[Tom Ogilvy]

Sub FindMatchedNames()
with Range("C2:C598").Offset(0,1)
.Formula = "=If(Countif($B$2:$B$1000,""*"" & C2 &
""*"")=0,""No"",""Yes"")"
.formula = .Value
End with
End suB

 

[Bob Kilmer]

It doesn't appear that you need the 'For Each t' line in the code you have
posted.

 

[Tom Ogilvy]

Just to correct for wordwrap

Sub FindMatchedNames()
with Range("C2:C598").Offset(0,1)
.Formula = _
"=If(Countif($B$2:$B$1000,""*"" & C2" & _
" & ""*"")=0,""No"",""Yes"")"
.formula = .Value
End with
End suB

 

[N10]

HI

Have you tried

application.screenupdating = false

at the start of the routine

n10

 

[Henry Stockbridge]

Thanks, everyone.

Now the procedure works like a charm.

Henry