Problem with Error handling in loop (with find)

[Wesslan]

I have created the following peace of code:

'Finds company within the peer group
On Error Goto CompanyNotPartofPeerGroup
Cells.Find(What:=ExcludedCompany, After:=Cells(1, 1),
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

'Eliminates the analyzed company if it is truly part of the peer
group
ActiveCell.FormulaR1C1 = "=ROW()"
temp = ActiveCell.Value
Rows(temp).Delete shift:=xlUp

CompanyNotPartofPeerGroup:
On Error Goto 0


The problem is that this works the first time the code is run (so if
the company is not part of the peer group a row is not deleted, but
rather it goes to CompanyNotPartofPeerGroup). But the second time the
code is run (part of the loop) and if the company analyzed is not part
of the peer group, then the Error Handling breaks down and instead of
going to CompanyNotPartofPeerGroup it handles the procedure as if I
had written "On Error Resume Next"...

Any ideas?

 

[Bob Phillips]

Dim cell As Range

'Finds company within the peer group
Set cell = noting
Set cell = Cells.Find(What:=ExcludedCompany, _
After:=Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If rng Is Nothing Then
cell.FormulaR1C1 = "=ROW()"
temp = cell.Value
Rows(temp).Delete shift:=xlUp
End If


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Don Guillett]

This seemed to run fine each time. I changed to xlwhole and xlvalues but
that shouldn't make a difference.
For multiple deletions try FINDNEXT

Sub NoFindIt()
excludedcompany = "msft"

On Error GoTo CompanyNotPartofPeerGroup
Cells.Find(What:=excludedcompany, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False) _
..EntireRow.Delete Shift:=xlUp

CompanyNotPartofPeerGroup:
On Error GoTo 0
End Sub

From Help
The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each
time you use this method. If you don't specify values for these arguments
the next time you call the method, the saved values are used. Setting these
arguments

 

[Dave Peterson]

I think Bob changed horses in midstream with his cell/rng variables.

Dim cell As Range

'Finds company within the peer group
'corrected spelling of nothing, but this line isn't necessary
'Set cell = nothing
Set cell = Cells.Find(What:=ExcludedCompany, _
After:=Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

'corrected logic. Bob left out Not
' "if not (cell is nothing) then"
'but I like:
If cell Is Nothing Then
'do nothing, it wasn't found
else
cell.FormulaR1C1 = "=ROW()"
temp = cell.Value
Rows(temp).Delete shift:=xlUp
End If

And I'd use this in the bottom portion of the code -- instead of using a
formula.

If cell Is Nothing Then
'do nothing, it wasn't found
else
cell.entirerow.delete
End If

 

[Wesslan]

Thanks a lot Bob!

You've made my day!

Dim cell As Range

    'Finds company within the peer group
    Set cell = noting
    Set cell = Cells.Find(What:=ExcludedCompany, _
                          After:=Cells(1, 1), _
                          LookIn:=xlFormulas, _
                          LookAt:=xlPart, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlNext, _
                          MatchCase:=False, _
                          SearchFormat:=False)
    If rng Is Nothing Then
        cell.FormulaR1C1 = "=ROW()"
        temp = cell.Value
        Rows(temp).Delete shift:=xlUp
    End If

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)











- Show quoted text -