Finding Values Too Slow
 

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

Finding Values Too Slow
 

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

--
Regards,
Tom Ogilvy

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

Finding Values Too Slow
 

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

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

Finding Values Too Slow
 

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

--
Regards,
Tom Ogilvy

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

--
Regards,
Tom Ogilvy

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

Finding Values Too Slow
 

HI

Have you tried

application.screenupdating = false

at the start of the routine

n10:)

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

Finding Values Too Slow
 

Thanks, everyone.

Now the procedure works like a charm.

Henry