Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hope this is easy | Excel Discussion (Misc queries) | |||
Validation I Hope | Excel Worksheet Functions | |||
Hope someone can help me | Excel Discussion (Misc queries) | |||
Hope someone here can help me | Excel Discussion (Misc queries) | |||
I hope I ask this correctly | Excel Programming |