Error while creating a chart with VBA

**JMicke** · 02-09-2012, 07:27 AM

I'm gettin an error, translated from swedish: "Objectvariable or With-blockvariable not set".
Does anyone know what I'm doing wrong? I'm using Excel 2010.
I'm including 2 files. Data contains the macro code.
Both files must be started. "Data" copy some cells to "HM", and creats a chart based on the values.
I include some of the code here:

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
    Set ChrtSrs1 = ActiveChart.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 = ActiveChart.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 = ActiveChart.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 = ActiveChart.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

    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

I get the error on the line "Set ChrtSrs1 = ActiveChart.SeriesCollection.NewSeries", almost at the end of the sub MergeWS

**Andy Pope** · 02-09-2012, 07:34 AM

You need to activate the chart if you are going to use the Activechart object.

Add extra line to code at end of MergeWS routine.

    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