?
Solved

Error Handler

Posted on 2012-04-11
2
Medium Priority
?
280 Views
Last Modified: 2012-06-27
Hi,

I have the below code.

When there is no closed Items, so the filter returns blank, i want to exit the sub with a message box saying "No closed Items"

Can someone advise where i would slot this in?

Thanks
Seamus








Private Sub CommandButton1_Click()

Dim rData As Range

Application.ScreenUpdating = False

With ActiveSheet
    .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=12, Criteria1:="Closed"
    With .AutoFilter.Range
        On Error Resume Next
        Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo ErrHandler
        If Not rData Is Nothing Then
            rData.EntireRow.copy
        End If
           

    End With
 
End With
 
Call copyOver
 
Application.ScreenUpdating = False





End Sub
0
Comment
Question by:Seamus2626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 37831975
Seamus - try this:
Private Sub CommandButton1_Click()

Dim rData As Range

Application.ScreenUpdating = False

With ActiveSheet
    .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=12, Criteria1:="Closed"
    With .AutoFilter.Range
        On Error Resume Next
        Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rData Is Nothing Then
            rData.EntireRow.Copy
        Else
            MsgBox "No closed items"
            Exit Sub
        End If
    End With
End With
 
Call copyOver
 
Application.ScreenUpdating = False

End Sub

Open in new window

0
 

Author Closing Comment

by:Seamus2626
ID: 37832008
Thanks Stephen!

Seamus
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question