Results 1 to 5 of 5

Avoiding Run Time Error

Threaded View

  1. #1
    Forum Contributor
    Join Date
    05-20-2013
    Location
    Ann Arbor, MI
    MS-Off Ver
    Excel 2010
    Posts
    255

    Avoiding Run Time Error

    Hello,

    I currently have a spread sheet where the user enters 'y' in the G column. If a y is entered, the user can then press the 'Update' button, and the rows containing a y in the g column will be moved to a different sheet, 'History'.

    It works fine if there are y's entered into the column, but I want to be able to avoid the user getting: "Run-Time Error '1004': no cells were found" when no y's are entered into the g column.

    The current code is this:
    Private Sub UpdateHistory2_Click()
        Application.ScreenUpdating = False
        
        Dim ws As Worksheet, ms As Worksheet
        Set ws = Sheets("Current Contractors")
        Set ms = Sheets("History Contractors")
    
        ws.ListObjects("Table1").Range.AutoFilter
    
        With ms
            'heading:
            .Range(.Cells(1, 1), .Cells(1, 8)).Value = Array("Today's Date", _
                                                             "Purchase Order Date", _
                                                             "Contractor", _
                                                             "Equipment", _
                                                             "Description", _
                                                             "Expected Time of Completion", _
                                                             "Completed? y or n", _
                                                             "Time of Completion")
        
        End With
        With ws.Range("A1:H" & ws.Cells.Find("*", , , , xlByRows, xlPrevious).Row)
                
                .AutoFilter 7, "y"
                .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).Copy
                ms.Range("A" & ms.Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
                .Resize(.Rows.Count - 1).Offset(1).EntireRow.Delete Shift:=xlUp
                .AutoFilter
            
        End With
        
        Application.ScreenUpdating = True
        
    End Sub
    I have attached my work book. The following code is for Current Contractors and History Contractors, although almost the exact code is being used for Maintenance Sheets.

    Please Help
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

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