help with 'Find' error handling please
 

Hi All,

The code pasted below loops thru a list, finds that value in a list on
another sheet and deletes the entire row. I need to put an error
handler in so that if the value is not found a msg box pops up to
inform the user then continues the loop

All help gratefully received.

Dim delName As String
Dim lr As Long
Dim ur
Sheets("TemporaryList").Activate
Range("A1").Activate
ur = Range(Selection, Selection.End(xlDown)).Address
Range(ur).AdvancedFilter Action:=xlFilterInPlace,
CopyToRange:=Columns( _
"E:E"), Unique:=True
Range("A2").Activate
Do Until ActiveCell.Value = ""
Sheets("TemporaryList").Activate
delName = ActiveCell.Value
Sheets("Current").Activate
With Worksheets("Current").Range("A:A")
Set c = .Find(delName, lookin:=xlValues)
c.EntireRow.Delete
End With
Sheets("TemporaryList").Activate
ActiveCell.Offset(1, 0).Activate
Loop
MsgBox ("finished")
Regards

Michael Beckinsale

help with 'Find' error handling please
 

With Worksheets("Current").Range("A:A")
Set c = .Find(delName, lookin:=xlValues)
if c is Nothing then
MsgBox "Search Value was not found"
else
c.EntireRow.Delete
End if
End With

HTH

Die_Another_Day
michael.beckinsale wrote:
Hi All,

The code pasted below loops thru a list, finds that value in a list on
another sheet and deletes the entire row. I need to put an error
handler in so that if the value is not found a msg box pops up to
inform the user then continues the loop

All help gratefully received.

Dim delName As String
Dim lr As Long
Dim ur
Sheets("TemporaryList").Activate
Range("A1").Activate
ur = Range(Selection, Selection.End(xlDown)).Address
Range(ur).AdvancedFilter Action:=xlFilterInPlace,
CopyToRange:=Columns( _
"E:E"), Unique:=True
Range("A2").Activate
Do Until ActiveCell.Value = ""
Sheets("TemporaryList").Activate
delName = ActiveCell.Value
Sheets("Current").Activate
With Worksheets("Current").Range("A:A")
Set c = .Find(delName, lookin:=xlValues)
c.EntireRow.Delete
End With
Sheets("TemporaryList").Activate
ActiveCell.Offset(1, 0).Activate
Loop
MsgBox ("finished")
Regards

Michael Beckinsale

help with 'Find' error handling please
 

Hi Die_Another_Day

Many thanks.

Reagrds

Michael beckinsale