Programing with a "Find"

  • Thread starter Thread starter PGibson
  • Start date Start date
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
 
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
 
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
 
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
 
Back
Top