Prev: Z order problems
Next: SUMIF criteria
From: stevieb on 4 May 2010 12:23
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
From: stevieb on 4 May 2010 12:32
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
From: stevieb on 4 May 2010 13:15
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
 | 
Pages: 1
Prev: Z order problems
Next: SUMIF criteria