Run-time error ‘91’:

[GCF]

I made this code as simple as possible to get to the bottom of th
problem. Now instead of failing some of the tine, it dies all th
time. Any insight please?

Sub Macro1()
'
For i = 1 To 1000
Columns("A:A").Select
FoundIt = Selection.Find(What:=i, _

After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate

i = i + 1

Next

End Su

 

[kkknie]

The problem is when the Find function doesn't find anything. If nothin
is found, the error is generated. The way around it is to use an erro
handler:

Sub Macro1()
Dim i As Integer
Dim FoundIt

For i = 1 To 1000
Columns("A:A").Select
ON ERROR RESUME NEX
FoundIt = Selection.Find(What:=i, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate

'Here you can check for an error and do what you need to do
If Err.Number <> 0 Then
'Do something
End If
i = i + 1

Next

End Sub

 

[Tom Ogilvy]

An alternative to using an error handler is - don't try to select nothing.

Sub Macro1()
Dim FoundIt as Range
For i = 1 To 1000
set FoundIt = Columns("A:A").Find(What:=i, _
After:=Range("A65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not FoundIt is nothing then
' operate on Foundit
' msgbox Foundit.Address
'
' Foundit.Select
Else
' target not found
end if
i = i + 1
Next

End Sub

If you want to progressively search down column A, then change
After:=Range("A65536") back to After:=ActiveCell and put
Range("A65536").Select at the top of the code.