Ignore plotting a series if all y-values are NA()

[michaelbails]

Hi,

I have a conditional charting code set up such that if a certain value is greater than some threshold, only that value will be plotted with a larger marker. For example, I'm looking at the peak pressure provided from a test. If the value of the pressure is greater than 20 (but less than 40) in the data set, I will plot that specific value with a marker that is a different shape (say diamond), size (10), and color (say gray). If pressure is normal, the markers will be square,5,pink.

The code I use is fairly simple:

ActiveCell.FormulaR1C1 = "=if(and(rc[-1]>20,rc[-1]<40),rc[-2],NA())"

so a #NA is placed into the P20 column if pressure does NOT exceed the value of 20.

My problem is that there may be a data set where none of the pressures are greater than 20 which leads to an error. Is there a way to avoid this. Here is my code for plotting the P20 data set on top of the other data set (using the same xValues):

    
Sheets("PreTraining").Select
        Cells.Find(What:=ln, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Select
    ActiveCell.Offset(2, 2).Select
    xr1 = ActiveCell.Row
    xc1 = ActiveCell.Column
    x1 = "r" & xr1 & "c" & xc1
    Selection.End(xlDown).Select
    xr2 = ActiveCell.Row
    xc2 = ActiveCell.Column
    x2 = "r" & xr2 & "c" & xc2
    ActiveCell.Offset(0, 1).Select
    yr2 = ActiveCell.Row
    yc2 = ActiveCell.Column
    y2 = "r" & yr2 & "c" & yc2
    Selection.End(xlUp).Select
    yr1 = ActiveCell.Row
    yc1 = ActiveCell.Column
    y1 = "r" & yr1 & "c" & yc1
    ActiveCell.Offset(0, 2).Select
    yr120 = ActiveCell.Row
    yc120 = ActiveCell.Column
    y120 = "r" & yr120 & "c" & yc120
    Selection.End(xlDown).Select
    yr220 = ActiveCell.Row
    yc220 = ActiveCell.Column
    y220 = "r" & yr220 & "c" & yc220
    ActiveCell.Offset(0, 1).Select
    yr240 = ActiveCell.Row
    yc240 = ActiveCell.Column
    y240 = "r" & yr240 & "c" & yc240
    Selection.End(xlUp).Select
    yr140 = ActiveCell.Row
    yc140 = ActiveCell.Column
    y140 = "r" & yr140 & "c" & yc140

    Sheets("Graphical Results").Select
        With ActiveChart.SeriesCollection.NewSeries
            .Name = ln
            .Values = "=" & pr & y1 & ":" & y2
            .XValues = "=" & pr & x1 & ":" & x2
        End With
    ActiveChart.SeriesCollection(5).Select
        With Selection.Border
            .Weight = xlThin
            .LineStyle = xlNone
        End With
        With Selection
            .MarkerBackgroundColorIndex = 38
            .MarkerForegroundColorIndex = 38
            .MarkerStyle = MyMarkers(1)
            .Smooth = True
            .MarkerSize = 5
            .Shadow = False
        End With
    Sheets("Graphical Results").Select
        With ActiveChart.SeriesCollection.NewSeries
            .Name = "P20"
            .Values = "=" & pr & y120 & ":" & y220
            .XValues = "=" & pr & x1 & ":" & x2
        End With
    ActiveChart.SeriesCollection(6).Select
        With Selection.Border
            .Weight = xlThin
            .LineStyle = xlNone
        End With
        With Selection
            .MarkerBackgroundColorIndex = 16
            .MarkerForegroundColorIndex = 16
            .MarkerStyle = MyMarkers(3)
            .Smooth = True
            .MarkerSize = 10
            .Shadow = False
        End With

I guess the basic solution would be to run a loop of the data where the pressure greater than 20 is populated and if there is no value (or only #NA), then I write an if/then statement around the additional charting series code above?

Hope this isn't too confusing. Please let me know if more information is necessary.

Best,

Mike

[masteff]

I see a few choices which could be combined with an if statement to bypass the chart related code. COUNTIF() being the easiest I can think of. COUNTIF({range for P20 data},"<>NA") would be equal to zero if all values were NA.
Alternatively you could have a variable in your code that gets flipped on if the data is not NA. So at the end of the data loop, you'd check if it was flipped and then chart as appropriate.