All the series lines in the chart are drawn on the same line

**JMicke** · 02-10-2012, 02:50 AM

I don't know what is wrong with my code.
Does anyone know what I have done wrong?

I include most of the code:

Sub MergeWS(wbK As Workbook, wbM As Workbook, blad As String)
'
    Dim LastRow As Long
    Dim NextRow As Long
    Dim Ant As Long
    Dim TotAnt As Long
    Dim cnt As Long
    
    On Error GoTo ErrorG
    
    wbM.Worksheets(blad).Activate
    Range("B3").Select
    ActiveCell.Font.Name = "Calibri"
    ActiveCell.Font.Bold = True
    Range("B3") = "n"
    
    'wbM.Worksheets(blad).Activate
    Range("C3").Select
    ActiveCell.Font.Name = "Calibri"
    ActiveCell.Font.Bold = True
    ActiveCell.Borders.LineStyle = xlContinuous
    Range("C3") = "Mätvärden"
    
    ' determine where the data ends on Column C Sheet1
    wbK.Worksheets("blad1").Activate
    Range("C65536").Select
    ActiveCell.End(xlUp).Select 'xlup
    LastRow = ActiveCell.Row
    
    ' copy the data from Column C in Sheet 1
    Range("C11:C" & LastRow).Copy
    Ant = LastRow - 10
    
    ' Determine where to add the new data in Column C Sheet 2
    wbM.Worksheets(blad).Activate
    Range("C65536").Select
    ActiveCell.End(xlUp).Offset(1, 0).Select
    NextRow = ActiveCell.Row
    
    ' paste the data to Column C Sheet 2
    wbM.Worksheets(blad).Range("C" & NextRow).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    For cnt = NextRow To (NextRow + Ant - 1)
        Range("B" + CStr(cnt)) = CStr(cnt - 3)   ' CStr(cnt - NextRow + 1)
    Next
    
    wbM.Worksheets(blad).Activate
    Range("C65536").Select
    ActiveCell.End(xlUp).Offset(1, 0).Select
    LastRow = ActiveCell.Row - 1
    
    Range("C3:C" & LastRow).Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    TotAnt = LastRow - 3
    AddChart wbM, blad, "C4", "C" + CStr(LastRow), TotAnt
    
    Range("F22").Select
    ActiveCell.Font.Name = "Calibri"
    ActiveCell.Font.Bold = True
    Range("F22") = "Kontrollprov"
    
    Range("H22").Select
    ActiveCell.Font.Name = "Calibri"
    ActiveCell.Font.Bold = True
    Range("H22") = "Sigma"
    
    Range("I22").Select
    ActiveCell.Font.Name = "Calibri"
    ActiveCell.Font.Bold = True
    Range("I22") = "Medel"
    
    Range("J22").Select
    ActiveCell.Font.Name = "Calibri"
    ActiveCell.Font.Bold = True
    Range("J22") = "2s_ö"
    
    Range("K22").Select
    ActiveCell.Font.Name = "Calibri"
    ActiveCell.Font.Bold = True
    Range("K22") = "2s_u"
    
    Range("L22").Select
    ActiveCell.Font.Name = "Calibri"
    ActiveCell.Font.Bold = True
    Range("L22") = "3s_ö"
    
    Range("M22").Select
    ActiveCell.Font.Name = "Calibri"
    ActiveCell.Font.Bold = True
    Range("M22") = "3s_u"
    
    Range("F23").Select
    ActiveCell.Font.Name = "Calibri"
    Range("F23") = blad
    
    ' Beräkna sigma ( std. avvikelse)
    Range("H23").Select
    ActiveCell.Font.Name = "Calibri"
    'Range("H23").Formula = "=stdev(C4:C" + CStr(LastRow) + ")"
    Range("H23").Formula = "=round(stdev(C4:C" + CStr(LastRow) + "), 2)"

    Range("I23").Select
    ActiveCell.Font.Name = "Calibri"
    Range("I23").Formula = "=average(C4:C" + CStr(LastRow) + ")"
    
    Range("K23:K" + CStr(22 + TotAnt)).Select
    ActiveCell.Font.Name = "Calibri"
    Range("K23:K" + CStr(22 + TotAnt)) = "213,74"
    
    Range("M23:M" + CStr(22 + TotAnt)).Select
    ActiveCell.Font.Name = "Calibri"
    Range("M23:M" + CStr(22 + TotAnt)) = "211,12"
    
    Range("J23:J" + CStr(22 + TotAnt)).Select
    ActiveCell.Font.Name = "Calibri"
    Range("J23:J" + CStr(22 + TotAnt)) = "224,17"
    
    Range("L23:L" + CStr(22 + TotAnt)).Select
    ActiveCell.Font.Name = "Calibri"
    Range("L23:L" + CStr(22 + TotAnt)) = "226,78"
    
    Dim ChrtSrs1 As Series, ChrtSrs2 As Series, ChrtSrs3 As Series, ChrtSrs4 As Series
    ' Activate chart first
    ActiveSheet.ChartObjects(1).Activate

    Set ChrtSrs1 = ActiveSheet.ChartObjects(1).Chart.SeriesCollection.NewSeries
    With ChrtSrs1
        .Name = "2s_u"
        .XValues = Worksheets(blad).Range("B4:B" + CStr(3 + TotAnt))
        .Values = Worksheets(blad).Range("K23:K" + CStr(22 + TotAnt))
    End With
    Set ChrtSrs2 = ActiveSheet.ChartObjects(1).Chart.SeriesCollection.NewSeries
    With ChrtSrs2
        .Name = "3s_u"
        .XValues = Worksheets(blad).Range("B4:B" + CStr(3 + TotAnt))
        .Values = Worksheets(blad).Range("M23:M" + CStr(22 + TotAnt))
    End With
    Set ChrtSrs3 = ActiveSheet.ChartObjects(1).Chart.SeriesCollection.NewSeries
    With ChrtSrs3
        .Name = "2s_ö"
        .XValues = Worksheets(blad).Range("B4:B" + CStr(3 + TotAnt))
        .Values = Worksheets(blad).Range("J23:J" + CStr(22 + TotAnt))
    End With
    Set ChrtSrs4 = ActiveSheet.ChartObjects(1).Chart.SeriesCollection.NewSeries
    With ChrtSrs4
        .Name = "3s_ö"
        .XValues = Worksheets(blad).Range("B4:B" + CStr(3 + TotAnt))
        .Values = Worksheets(blad).Range("L23:L" + CStr(22 + TotAnt))
    End With

    Exit Sub
ErrorG:
    MsgBox Err.Description, vbInformation, ""
End Sub

Sub AddChart(wbM As Workbook, blad As String, first As String, last As String, TotAnt As Long)
   Dim chtChart As Chart
   On Error Resume Next
   wbM.Worksheets(blad).Activate
   ActiveSheet.ChartObjects.Delete
   'Create a new chart.
   Set chtChart = Charts.Add
   Set chtChart = chtChart.Location(Where:=xlLocationAsObject, Name:=blad)
   With chtChart
      .ChartType = xlLineStacked
      'Set data source range.
      .SetSourceData Source:=Sheets(blad).Range(first + ":" + last), PlotBy:=xlColumns
      .HasTitle = True
      .ChartTitle.Text = blad

      'The Parent property is used to set properties of
      'the Chart.
      With .Parent
        .Top = Range("E6").Top
        .Left = Range("E6").Left
        .Name = "Kontrollprov"
      End With
      Err.Clear
      wbM.Worksheets(blad).ChartObjects(1).Chart.SeriesCollection(1).Interior.Color = RGB(255, 0, 0)

      If Err Then MsgBox Err.Description
   End With
   
End Sub

If I understand how the chart works the different series lines should be drawn at different places with this code:
(and I have checked so the different cells does not contain the same values)

Dim ChrtSrs1 As Series, ChrtSrs2 As Series, ChrtSrs3 As Series, ChrtSrs4 As Series
    ' Activate chart first
    ActiveSheet.ChartObjects(1).Activate

    Set ChrtSrs1 = ActiveSheet.ChartObjects(1).Chart.SeriesCollection.NewSeries
    With ChrtSrs1
        .Name = "2s_u"
        .XValues = Worksheets(blad).Range("B4:B" + CStr(3 + TotAnt))
        .Values = Worksheets(blad).Range("K23:K" + CStr(22 + TotAnt))
    End With
    Set ChrtSrs2 = ActiveSheet.ChartObjects(1).Chart.SeriesCollection.NewSeries
    With ChrtSrs2
        .Name = "3s_u"
        .XValues = Worksheets(blad).Range("B4:B" + CStr(3 + TotAnt))
        .Values = Worksheets(blad).Range("M23:M" + CStr(22 + TotAnt))
    End With
    Set ChrtSrs3 = ActiveSheet.ChartObjects(1).Chart.SeriesCollection.NewSeries
    With ChrtSrs3
        .Name = "2s_ö"
        .XValues = Worksheets(blad).Range("B4:B" + CStr(3 + TotAnt))
        .Values = Worksheets(blad).Range("J23:J" + CStr(22 + TotAnt))
    End With
    Set ChrtSrs4 = ActiveSheet.ChartObjects(1).Chart.SeriesCollection.NewSeries
    With ChrtSrs4
        .Name = "3s_ö"
        .XValues = Worksheets(blad).Range("B4:B" + CStr(3 + TotAnt))
        .Values = Worksheets(blad).Range("L23:L" + CStr(22 + TotAnt))
    End With

I include a picture of how the chart looks like.
For the complete code, look at my earlier question at http://www.excelforum.com/excel-char...-with-vba.html

**JMicke** · 02-10-2012, 03:06 AM

Or maybe not drawn at the same line, but atleast not on the intended place.
I swapped places with the xvalues and values:

Set ChrtSrs1 = ActiveSheet.ChartObjects(1).Chart.SeriesCollection.NewSeries
    With ChrtSrs1
        .Name = "2s_u"
        .Values = Worksheets(blad).Range("B4:B" + CStr(3 + TotAnt))
        .XValues = Worksheets(blad).Range("K23:K" + CStr(22 + TotAnt))
    End With
    Set ChrtSrs2 = ActiveSheet.ChartObjects(1).Chart.SeriesCollection.NewSeries
    With ChrtSrs2
        .Name = "3s_u"
        .Values = Worksheets(blad).Range("B4:B" + CStr(3 + TotAnt))
        .XValues = Worksheets(blad).Range("M23:M" + CStr(22 + TotAnt))
    End With
    Set ChrtSrs3 = ActiveSheet.ChartObjects(1).Chart.SeriesCollection.NewSeries
    With ChrtSrs3
        .Name = "2s_ö"
        .Values = Worksheets(blad).Range("B4:B" + CStr(3 + TotAnt))
        .XValues = Worksheets(blad).Range("J23:J" + CStr(22 + TotAnt))
    End With
    Set ChrtSrs4 = ActiveSheet.ChartObjects(1).Chart.SeriesCollection.NewSeries
    With ChrtSrs4
        .Name = "3s_ö"
        .Values = Worksheets(blad).Range("B4:B" + CStr(3 + TotAnt))
        .XValues = Worksheets(blad).Range("L23:L" + CStr(22 + TotAnt))
    End With

when I run the code I get this chart Chart2.jpg

**Andy Pope** · 02-10-2012, 06:47 AM

Post an example of how you want the chart to look. What should be on the category axis , series name and values.

**JMicke** · 02-13-2012, 01:55 AM

The thought was that the chart should look like something like the attached chart Chart3.jpg
The names on the series is correct in my first chart, just not the position of the lines

**Andy Pope** · 02-13-2012, 04:26 AM

Try swapping the .Values and .XValues assignments.
XValues are the horizontal axis data points or Labels
Values are the vertical axis data positions

**JMicke** · 02-13-2012, 05:09 AM

I did that above. in the chart.jpg picture in my first post and swapped values and showed how the chart looked with swapped values in chart2.jpg in my second post.
(I also included the changed code in the 2 postings)

**Andy Pope** · 02-13-2012, 05:16 AM

Are the values true values or text. I assume the 224,17 was your regional setting for 224.17. So in my tests I converted the text to true numbers and create the chart you describe with horizontal lines for the repeating values.

**JMicke** · 02-13-2012, 05:33 AM

Yes, we use a "," sign as a decimal sign in sweden. The values are true values, and it correctly can count the average in cell I23.

**Andy Pope** · 02-13-2012, 05:44 AM

So if I convert the output values to use period rather than comma as decimal place holder and swap xvalues/values I get the following output.

**JMicke** · 02-13-2012, 06:15 AM

Hmm, very strange, seems your chart is ok, except for the line that is only shown in my chart.jpg, but I guess you didn't convert that line since its done in the sub Addchart.
I guess I'll have to think about this, thanks for the help.