Programing with a "Find"

P

PGibson

When a value is not found, the macro errors and stops. How can I trap the
error before it is reported and act on the error with out stopping the
macro?

Columns("F:F").Select
Selection.Find(What:="347", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=False).Activate
 
T

Tom Ogilvy

You tell it to activate, but if it hasn't found anything, what is is
supposed to activate. Remove the activate, remove the problem.

Dim rng as Range
set rng = Columns("F:F").Find(What:="347", _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False)
if not rng is nothing then
rng.activate
Else
msgbox "Not found"
End if
 
P

PGibson

for some reason I need to select the range before using the set rng. this
works with the range select the other errors with unable to set column
property ?? Can you explain?

Sub AddToTotals(FindText As String)
Range("f:f").Select
Set RNG = Columns("F:F").Find(What:=FindText, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=False)
If Not RNG Is Nothing Then
RNG.Activate
Call AddUp
Else
End If
End Sub
 
D

Dave Peterson

When you selected the column, the active cell was in that column.

I like this to find the first occurance in a range:

Option Explicit

Sub AddToTotals(FindText As String)
'Range("f:f").Select

Dim RNG As Range

With ActiveSheet.Columns("F:F")
Set RNG = .Cells.Find(What:=FindText, After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False)
End With
If Not RNG Is Nothing Then
RNG.Activate
Call AddUp
Else
End If
End Sub
 
Top