Find Method in For Loop

[Sisilla]

Hello All,

I am using the Find Method in a For Loop, and it is working as one
would expect when the "What" argument is set to a value that occurs in
the first 1097 rows of the search range. If the value occurs after the
1097th row, the method returns an empty range. Any ideas as to what
could be causing this?

I am running Windows XP Pro, Excel 2003 and VB 6.3. I appreciate any
effort to help me. Thank you for your time and consideration.

Sincerely,
Sisilla

 

[Jim Rech]

I didn't run into that limitation in a quick test. To duplicate what I did
fill in "abc" from A1 to A2000 and run this code:

Sub a()
Dim firstAddress As String
Dim c As Range
With Range("a1:a2000")
Set c = .Find("abc")
If Not c Is Nothing Then
firstAddress = c.Address
Do
Set c = .FindNext(c)
c.Offset(0, 1).Value = "x"
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

It puts an "x" next to all 2000 "abc"s

--
Jim
| Hello All,
|
| I am using the Find Method in a For Loop, and it is working as one
| would expect when the "What" argument is set to a value that occurs in
| the first 1097 rows of the search range. If the value occurs after the
| 1097th row, the method returns an empty range. Any ideas as to what
| could be causing this?
|
| I am running Windows XP Pro, Excel 2003 and VB 6.3. I appreciate any
| effort to help me. Thank you for your time and consideration.
|
| Sincerely,
| Sisilla
|

 

[Sisilla]

Jim,

Thank you for your reply. I appreciate your efforts to help me.

In your code, you are using the FindNext method after the first row,
but I am experiencing problems with the Find method. Let me explain a
little further about what I am attempting to do.

I am looping through each cell in a column. In each iteration, I am
searching for the value of that particular cell in another column
using the Find method. I do not think that I can use the FindNext
method in this situation, but I may be wrong. I appreciate any further
advice. Again, thanks.

Sincerely,
Sisilla

 

[Sisilla]

It turns out that I was limiting the search range to the first 1097
rows. There is no Excel limit. It's working now. Thank you for your
help, Jim.