Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,272
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hope this is easy RobertG Excel Discussion (Misc queries) 3 May 18th 10 07:13 PM
Validation I Hope MarsBars Excel Worksheet Functions 1 April 21st 09 09:22 AM
Hope someone can help me Jay Blatherwick Excel Discussion (Misc queries) 1 June 15th 06 06:03 PM
Hope someone here can help me AlienBeans Excel Discussion (Misc queries) 9 March 17th 06 01:37 AM
I hope I ask this correctly Mark Excel Programming 3 March 5th 04 04:23 PM


All times are GMT +1. The time now is 05:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"