Problem with Error handling in loop (with find)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Wesslan - 05 Feb 2008 12:15 GMT

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?

Reply to this Message

Bob Phillips - 05 Feb 2008 13:37 GMT

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

Signature

---
HTH

Bob

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

>I have created the following peace of code:
>
[quoted text clipped - 24 lines]
>
> Any ideas?

Reply to this Message

Wesslan - 05 Feb 2008 18:57 GMT

Thanks a lot Bob!

You've made my day!

> Dim cell As Range
>
[quoted text clipped - 52 lines]
>
> - Show quoted text -

Reply to this Message

Don Guillett - 05 Feb 2008 14:14 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>I have created the following peace of code:
>
[quoted text clipped - 24 lines]
>
> Any ideas?

Reply to this Message

Dave Peterson - 05 Feb 2008 14:55 GMT

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

> I have created the following peace of code:
>
[quoted text clipped - 24 lines]
>
> Any ideas?

Signature

Dave Peterson

Reply to this Message