Thread: Data line does not show (only sometimes) on macro updated scatter plot

I have a two list boxes that select a location and a person, I've written a macro that then updates the chart's range when the list box is altered, so that you can easly select the data to view and it pops up.

Works great except sometimes the data just doesn't show up. The chart x-axis values are updated, so is the title, and when I go to select data it says that the correct range is selected, but I'm still left with a blank scatter plot. Then when I change the graph type the data apears.

Seems to be an issue for only some of the data sets, the ones that don't work always don't work.

Anyone ever had this problem? Below is the code attached to the list boxes.

Thanks for any help,
Jack Rose

Sub ListBoxUpdate()
'This sub routine updates both list boxes, first counting the sites
'then counting the number of data sets before the staff selected in the list box,
'then it changes the chart range so that the correct data is displayed
'Written by Jack Rose for Winstone Aggregates
'Last edit: 13/1/12

Dim RCount As Integer, PlotCount As Integer, i As Integer, j As Integer, x As Integer, y As Integer, SiteNo As Integer
Dim Check As Boolean, b As Boolean
Dim TValues As String, S1Values As String, S2Values As String, StaffRange As String, SiteRange As String
Dim GraphMin As Date, GraphMax As Date

Application.ScreenUpdating = False

'Count sites
b = True
i = 2
Do While b = True
    If Sheets("Main").Range("AA" & i) <> "" Then
        i = i + 1
    Else
        b = False
    End If
Loop
i = i - 1       'Loop above counts one cell too far, have to reduce index by 1

'Set list box data
    Sheets("Main").Activate
    Sheets("Main").Shapes("List Box 6").Select
    With Selection
        .ListFillRange = "AA2:AA" & i
        .LinkedCell = "A1"
        .MultiSelect = xlNone
        .Display3DShading = True
    End With
    Sheets("Main").Range("A1").Activate

SiteNo = Sheets("Main").Range("A1").Value

'Count staff at site
b = True
i = 2
Do While b = True
    If Sheets("Main").Cells(i, SiteNo + 27) <> "" Then
        i = i + 1
    Else
        b = False
    End If
Loop

'Set list box data
StaffRange = Range(Cells(2, SiteNo + 27), Cells(i - 1, SiteNo + 27)).Address
    Sheets("Main").Shapes("List Box 9").Select
    With Selection
        .ListFillRange = StaffRange
        .LinkedCell = "A2"
        .MultiSelect = xlNone
        .Display3DShading = True
    End With
        ActiveSheet.Range("A1").Activate


x = Range("A2") + 1
y = Range("A1") + 27

i = 1
j = 28

Check = False

Do While Check = False

    'Go to next cell
    If Sheets("Main").Cells(i + 1, j) <> "" Then
        i = i + 1
    ElseIf Sheets("Main").Cells(i + 1, j) = "" Then
        i = 2
        j = j + 1
    End If

    
    'Check if desired cell has been reached yet
    If Sheets("Main").Cells(i, j).Address = Sheets("Main").Cells(x, y).Address Then
        Check = True
    End If
    
    RCount = RCount + 1
    
Loop

'Counting the amount of data to plot
Check = False
PlotCount = 3
Do While Check = False
    If Sheets("Plot data").Cells(PlotCount, RCount * 3) <> "" Then
        PlotCount = PlotCount + 1
    ElseIf Sheets("Plot data").Cells(PlotCount, RCount * 3) = "" Then
        Check = True
    End If
Loop
PlotCount = PlotCount - 1   'Removing the last empty cell off the range, (the loop goes too far)

'Output RCount
Sheets("Main").Range("A3") = RCount

'Update Chart Range
    
    TValues = Range(Cells(3, RCount * 3 - 2), Cells(PlotCount, RCount * 3 - 2)).Address
    S1Values = Range(Cells(3, RCount * 3), Cells(PlotCount, RCount * 3)).Address
    S2Values = Range(Cells(3, RCount * 3 - 1), Cells(PlotCount, RCount * 3 - 1)).Address
    TValue = Cells(1, RCount * 3 - 2).Address
    
    GraphMax = Worksheets("Plot data").Cells(PlotCount - 1, RCount * 3 - 2)
    GraphMin = Worksheets("Plot data").Cells(3, RCount * 3 - 2)
    
    Sheets("Main").ChartObjects("Chart 1").Activate
    With ActiveChart
        .SeriesCollection(1).XValues = "='Plot data'!" & TValues
        .SeriesCollection(1).Values = "='Plot data'!" & S1Values
        .SeriesCollection(2).XValues = "='Plot data'!" & TValues
        .SeriesCollection(2).Values = "='Plot data'!" & S2Values
        .ChartTitle.Text = "='Plot data'!" & TValue
    End With
    
    ActiveChart.Axes(xlCategory).MinimumScale = GraphMin - #12:10:00 AM#
    ActiveChart.Axes(xlCategory).MaximumScale = GraphMax + #12:10:00 AM#
    
    Sheets("Main").Range("A1").Activate
    
    'Update summary
    Sheets("Main").Range("C20") = Sheets("2011-12").Range("C" & RCount + 1)     'Name
    Sheets("Main").Range("C21") = Sheets("2011-12").Range("A" & RCount + 1)     'Site
    Sheets("Main").Range("C22") = Sheets("2011-12").Range("B" & RCount + 1)     'Date
    Sheets("Main").Range("C23") = Sheets("2011-12").Range("E" & RCount + 1)     'Job
    Sheets("Main").Range("C24") = Sheets("2011-12").Range("F" & RCount + 1)     'Machine
    Sheets("Main").Range("C25") = Sheets("2011-12").Range("I" & RCount + 1)     'LAeq
    Sheets("Main").Range("C26") = Sheets("2011-12").Range("K" & RCount + 1)     'LCpeak
    
    Application.ScreenUpdating = True
    
End Sub

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

The usual reason is chart data in hidden rows or columns. There is a chart setting which can be ticked to overcome this.

If the issue is only with some data sets then it is unlikely to be the code but rather the data and or chart.
Can you post example workbook that illustrates the problem