CODEWITHSUNDEEP
excelvbacharts
Peekay
Peekay

Reputation: 236

Charts with variable series in Excel VBA

I am new to VBA and to this forum. I have a table with dates as the first column (x column) and 12 columns of data pertaining to the data (y values). I am trying to plot the data in a simple xlLine chart. Only few selected columns are to be plotted for y values. The columns are selected using a combo box at the top of the column. The number of rows are variable.

I am using this code but this is not working. Can someone kindly let me know what is wrong and fix it? Appreciate any help. Thanks in advance.

Sub drawchart1()
'
' drawchart1 Macro
'

'
    Dim i As Integer
    Dim j As Integer
    Dim n As Integer

    ' finding the number of rows

    j = Range("Charts!A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row

    ' selecting some range and adding a chart which is then modified.(not sure this is the correct method.)

    Range("A10:C15").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine

    i = 2
    n = 2

    ' Cells (9,1) contains the value "Date". Defining the X Axis values

    ActiveChart.SeriesCollection(1).Name = Sheets("Charts").Cells(9, 1).Value
    ActiveChart.SeriesCollection(1).XValues = "=Charts!R10C1:R" & j & "C1"

    Do While i < 14

    ' Cells(8,i) contain the results of combo box - true or false. 
    ' Cells(9,i) contain the names of the series

    If Cells(8, i).Value = True Then
        ActiveChart.SeriesCollection(n).Name = Sheets("Charts").Cells(9, i).Value
        ActiveChart.SeriesCollection(n).Values = "=Charts!R10C" & i & ":R" & j & "C" & i
        n = n + 1
        i = i + 1
    Else
        i = i + 1
    End If
    Loop

End Sub

Hi Again,

Since my columns would not exceed 14 (i.e. not large), I used the following "brute force" technique and it worked fine. I would still love to learn how to do it without using the "brute force" technique. Thanks in advance.

Sub drawchart()

Dim j As Integer
Dim Chartstring As String

j = Range("Charts!A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row

Chartstring = "A9:A" & j

If Cells(8, 2).Value = True Then
    Chartstring = Chartstring & ", B9:B" & j
Else
    Chartstring = Chartstring
End If

If Cells(8, 3).Value = True Then
    Chartstring = Chartstring & ", C9:C" & j
Else
    Chartstring = Chartstring
End If

' And similarly added code for each of the 14 columns

' And finally fed the chartstring into the "Source"

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Range(Chartstring)

End Sub

Upvotes: 0

Views: 4443

Answers (1)

Jon Peltier
Jon Peltier

Reputation: 6063

Probably you're not watching any more. Here's an alternative approach.

Sub DrawChart1()
  Dim i As Long
  Dim j As Long
  Dim ws As Worksheet
  Dim rCht As Range, rYVals As Range
  Dim cht As Chart

  ' finding the number of rows
  Set ws = Worksheets("Charts")
  j = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

  ' start with X values (row 10 to j), include header row (row 9)
  Set rCht = ws.Range(ws.Cells(9, 1), ws.Cells(j, 1))

  ' add column of Y values if row 8 of column is TRUE
  For i = 2 To 14
    If ws.Cells(8, i).Value Then
      Set rYVals = ws.Range(ws.Cells(9, i), ws.Cells(j, i))
      Set rCht = Union(rCht, rYVals)
    End If
  Next

  ' if we've had any Y values, insert chart, using range we've built up
  If Not rYVals Is Nothing Then
    Set cht = ws.Shapes.AddChart(xlLine).Chart
    cht.SetSourceData Source:=rCht, PlotBy:=xlColumns
  End If
End Sub

Upvotes: 0

Related Questions