| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Finding Values Too Slow
Thanks, everyone.
Now the procedure works like a charm. Henry |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding the 3 best values | Excel Worksheet Functions | |||
| Finding Most Recent Values in Col1 -- Summing Matching Values | Excel Discussion (Misc queries) | |||
| Finding values | Excel Worksheet Functions | |||
| Finding values | Excel Worksheet Functions | |||
| finding values and displaying adjacent values | Excel Worksheet Functions | |||
Linear Mode
