Exit A Sub?? 1

[mbarnett]

Hi All,
I have a macro that reads in data from another sheet based on a specified criteria and then spits the data into another spreadsheet. Here is my problem - if nothing meets the criteria the macro crashes because there is no info. How can I make the macro exit, if nothing is there to pull out.


Thanks

Mark

 

[SkipVought]

Hi,
You might test up front to see if there is data and if not, then exit sub.

Whats your code and where does it crater?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mbarnett]

Hi,

It crashes in my print sub. What kind of test?

 

[SkipVought]

Something like this...

Sub atest()
    Dim HasData As Boolean
    HasData = False
    With ActiveSheet.UsedRange.Cells
        If .Count > 1 Then
            HasData = True
        Else
            If .Value <> &quot;&quot; Then
                HasData = True
            End If
        End If
    End With
    If HasData Then
        MsgBox &quot;go print'&quot;
    End If
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mbarnett]

Thanks Skip,

My criteria is based on anything greater than zero. There may be data with a zero value. If I use the test it still crashes. I'm a rookie at VBA, how can I modify your code for my criteria. Example

Td cf
53215 0

if all values in cf are zero then skip it otherwise print the value. For the first time the file had all zeros so my code crashed. Can I use a function to test it? I'm not sure how to do that.....

 

[SkipVought]

What is cf? A single value a range of values. What are you doing with cf such that it would affect a print macro.

C'mon, help me out here!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mbarnett]

Hi Skip,

Cf is a range of cell, my macro is reading the data based on a criteria. If the value in CF column is not equal to zero then return the value in the print sub. The problem in my print sub because if all the values in cf are zero I get an error messege that the value is empty.

 

[SkipVought]

Simple then

If Application.Sum(Range(&quot;cf&quot;)) <> 0 Then YourPrintSub

assuming that the range is named "cf"



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mbarnett]

Thanks Skip,

I appreciate your help. It works.

 

[xlbo]

mbarnett - looks like Skip helped you out there. The way to say thanks in TekTips is to award a star. This also helps those people who search the archives to narrow down their searches to those threads with stars - which would indicate helpful information / resolution of the question. To award a star, simply click on the "Mark this post as a helpful / expert post" link at the bottom of the appropriate thread

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244

 

[mbarnett]

Thanks for the info xlbo