Results 1 to 3 of 3

Find Function when there is nothing to find

Threaded View

  1. #1
    Registered User
    Join Date
    08-14-2015
    Location
    UK
    MS-Off Ver
    2013
    Posts
    10

    Find Function when there is nothing to find

    I have put together some code to organise data from a time-sheet report. I want to delete the rows labelled as Sick Leave, Annual Leave and Other Leave as these times are divided proportionally between the projects people are working on. to do this I have used the Find function to locate the row and delete it. 9/10 times the headings will appear on the report as people will have clocked something against them but there is a chance that there will be nothing clocked against these headings so they won't appear on the report so the find function produces and error. I have tried using On Error to skip the line of code but i cant get it to work. I have pasted what I have below any help on the matter would be greatly appreciated.

      On Error GoTo Err1
        Cells.Find(What:="Sick Leave (BZ8800)", After:=ActiveCell, LookAt:=xlWhole, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Select
        ActiveCell.EntireRow.Delete
    Err1:
        On Error GoTo Err2
        Cells.Find(What:="Annual Leave (BZ8800)", After:=ActiveCell, LookAt:=xlWhole, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Select
        ActiveCell.EntireRow.Delete
    Err2:
        On Error GoTo Err3
        Cells.Find(What:="Other Leave (BZ8800)", After:=ActiveCell, LookAt:=xlWhole, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Select
        ActiveCell.EntireRow.Delete
    Err3:
        Sheets("Sick-Holiday").Select
        On Error GoTo Err4
        Cells.Find(What:="Sick Leave (BZ8800)", After:=ActiveCell, LookAt:=xlWhole, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Select
        ActiveCell.EntireRow.Delete
    Err4:
        On Error GoTo Err5
        Cells.Find(What:="Annual Leave (BZ8800)", After:=ActiveCell, LookAt:=xlWhole, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Select
        ActiveCell.EntireRow.Delete
    Err5:
        On Error GoTo Err6
        Cells.Find(What:="Other Leave (BZ8800)", After:=ActiveCell, LookAt:=xlWhole, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Select
        ActiveCell.EntireRow.Delete
    Err6:
        Sheets("Time").Select
        On Error GoTo Err7
        Cells.Find(What:="Sick Leave (BZ8800)", After:=ActiveCell, LookAt:=xlWhole, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Select
        ActiveCell.EntireRow.Delete
    Err7:
        On Error GoTo Err8
        Cells.Find(What:="Annual Leave (BZ8800)", After:=ActiveCell, LookAt:=xlWhole, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Select
        ActiveCell.EntireRow.Delete
    Err8:
        On Error GoTo Err9
        Cells.Find(What:="Other Leave (BZ8800)", After:=ActiveCell, LookAt:=xlWhole, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Select
        ActiveCell.EntireRow.Delete
    Err9:
        Sheets("Project Charge").Select
        Range("A1").Select
    Last edited by johnmahon12; 09-06-2016 at 05:03 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Can I use FIND function to find specific string in a single cell?
    By bonpara in forum Excel General
    Replies: 2
    Last Post: 10-05-2015, 05:46 PM
  2. add in the parameters to the Find function to only find an exact match.
    By jakeembx in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-07-2014, 08:57 AM
  3. Help with a function to Find, insert and find next
    By Boomstick in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-17-2013, 03:53 PM
  4. Range.Find function fails to find a match but For loop confirms that match exists
    By 6StringJazzer in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-08-2013, 03:35 PM
  5. [SOLVED] Problem with Find function. Need to find Exact match
    By SMILE in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-06-2012, 07:07 AM
  6. find function doesnt find imported info
    By dscott2479 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 07-12-2011, 07:11 PM
  7. Using find function to find entries in multiple cells
    By stanigator in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-11-2007, 08:45 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1