Problem with Error handling in loop (with find)

  • Thread starter Thread starter Wesslan
  • Start date Start date
W

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?
 
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)
 
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
 
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
 
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 -
Click to expand...
 
Back
Top