Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the FIND function in a loop
Hello,
I have two worksheet x and y. x - Column C 1 2 3 4 5 y - Column C 1 1 2 3 2 1 1 I am trying to use the FIND function to traverse through y - Column C and return the row value of the match in x - Column C to y - Column D. Here is what I have so far. Note that it fails with the following message for large record #'s: Code execution has been interrupted. Dim rFound As Range For i = 1 To 50000 Set rFound = Columns(3).Find(What:="" & Sheets("x").Cells(i, 3), After:=Cells(2, 3), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) Sheets("y").Cells(i,4) = rFound.Row Next i I think I may have reached some sort of system limitation. Thanks in advance, -Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the FIND function in a loop
Edit: Made a typo..
What:="" & Sheets("x").Cells(i, 3) should read as What:="" & Sheets("y").Cells(i, 3) "stevieb" wrote: Hello, I have two worksheet x and y. x - Column C 1 2 3 4 5 y - Column C 1 1 2 3 2 1 1 I am trying to use the FIND function to traverse through y - Column C and return the row value of the match in x - Column C to y - Column D. Here is what I have so far. Note that it fails with the following message for large record #'s: Code execution has been interrupted. Dim rFound As Range For i = 1 To 50000 Set rFound = Columns(3).Find(What:="" & Sheets("x").Cells(i, 3) , After:=Cells(2, 3), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) Sheets("y").Cells(i,4) = rFound.Row Next i I think I may have reached some sort of system limitation. Thanks in advance, -Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the FIND function in a loop
Edit #2: Problem solved.
i had to include a couple of lines of code that reset the FIND paramteres before the enxt iteration. Set rFound = Columns(3).Find("", LookIn:=xlValues, lookat:=xlWhole, _ searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False) Set rFound = Nothing I know this is not optimal but it worked for the time being. "stevieb" wrote: Edit: Made a typo.. What:="" & Sheets("x").Cells(i, 3) should read as What:="" & Sheets("y").Cells(i, 3) "stevieb" wrote: Hello, I have two worksheet x and y. x - Column C 1 2 3 4 5 y - Column C 1 1 2 3 2 1 1 I am trying to use the FIND function to traverse through y - Column C and return the row value of the match in x - Column C to y - Column D. Here is what I have so far. Note that it fails with the following message for large record #'s: Code execution has been interrupted. Dim rFound As Range For i = 1 To 50000 Set rFound = Columns(3).Find(What:="" & Sheets("x").Cells(i, 3) , After:=Cells(2, 3), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) Sheets("y").Cells(i,4) = rFound.Row Next i I think I may have reached some sort of system limitation. Thanks in advance, -Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Do loop with find | Excel Programming | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
Find Loop and then Loop again | Excel Programming | |||
Loops to find blanks then loop to find populated | Excel Programming | |||
Loop Function unable to loop | Excel Programming |