Last Hope -HELP!!
Marie,
I have found that using Find on a subsequent miss the find range object
does not get set to Nothing, so I do it explicitly. Try this
Sub DeleteApp()
Worksheets("EMPLOYEES").Select
For Each Cell In Range("List")
With Range("BranchToDistrict")
Set c = .Find(Cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
pp = Range("districtnumber").Value
ppp = c.Offset(0, 1).Value
If c.Offset(0, 1).Value = Range("DistrictNumber").Value Then
Offset(0, 1).Value
Else: Selection.EntireRow.Delete
End If
Else
'do nothing
End If
Set c = Nothing
End With
Next Cell
End Sub
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Marie" wrote in message
...
I have posted this before and everybody recommends using vlookup - which I
can't because I have rows and rows of coding depending on this criteria. I
don't know how to write the first rows of the code to tell it to only pick
up certain numbers and delete the rest. I have a sheet that I have listed
all the branch numbers I need and the column next to it with the district
numbers. This is the code I wrote but it deletes everything instead of just
the right branch numbers. Somebody please help!!! I am going nuts!!
Sub DeleteApp()
Worksheets("EMPLOYEES").Select
For Each Cell In Range("List")
Cell.Value = Cell.Value
D = Cell.Value
With Range("BranchToDistrict")
Set c = .Find(D, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
pp = Range("districtnumber").Value
ppp = c.Offset(0, 1).Value
If c.Offset(0, 1).Value = Range("DistrictNumber").Value
Then
Offset(0, 1).Value
Else: Selection.EntireRow.Delete
End If
Else
'do nothing
End If
End With
Next Cell
End Sub
|