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

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

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