Fout bij maken grafiek

[BasHD]

Ik heb de volgende code:

Sub MaakGrafiek()
    
 Dim Msg, Style, Title, Help, Ctxt, Response, MyString, v, XKolom, YKolom, STXKolom, STYKolom
 Dim LastRow, Tekst1, Tekst2, Tekst3
 
    Sheets("Blad1").Select
    
    [C6].Select
    XKolom = ActiveCell.Cells - 1
    [F6].Select
    YKolom = ActiveCell.Cells - 1
    
    [BC6].Select
    STXKolom = ActiveCell.Cells - 1
    [BF6].Select
    STYKolom = ActiveCell.Cells - 1
    Sheets("Blad1").Select
        
    [B6].Select
    Tekst1 = ActiveCell.Cells
    [E6].Select
    Tekst2 = ActiveCell.Cells
    [C4].Select
    Tekst3 = ActiveCell.Cells
     
    Msg = "BLGG of SoilTech : BLGG dan Ja, SoilTech dan Nee"
    Style = vbYesNo + vbCritical + vbDefaultButton2
    Title = "Gras of Snijmias"
    
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    If Response = vbYes Then
        v = "InvoerAccess"
    Else
        v = "InvoerSoiltech"
    End If
    
    
    LastRow = Sheets(v).Cells(Rows.Count, "G").End(xlUp).Row
    
    If v = "InvoerSoiltech" Then GoTo GrafiekSoiltech
    
    Charts.Add
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=Sheets("Blad1").Range("C4"), PlotBy:= _
        xlRows
    ActiveChart.SeriesCollection.NewSeries
    
    ActiveChart.SeriesCollection(1).XValues = "=" & v & "!R3C[" & XKolom & "]:R[" & LastRow & "]C[" & XKolom & "]"
    [COLOR="red"]ActiveChart.SeriesCollection(1).Values = "=" & v & "!R3C[" & YKolom & "]:R[" & LastRow & "]C[" & YKolom & "]"[/COLOR]
    ActiveChart.SeriesCollection(1).Name = "=Blad1!R4C3"
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Blad1"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = Tekst3
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = Tekst1
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = Tekst2
    End With
    With ActiveChart
        .HasAxis(xlCategory, xlPrimary) = True
        .HasAxis(xlValue, xlPrimary) = True
    End With
    ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
    With ActiveChart.Axes(xlCategory)
        .HasMajorGridlines = False
        .HasMinorGridlines = False
    End With
    With ActiveChart.Axes(xlValue)
        .HasMajorGridlines = False
        .HasMinorGridlines = False
    End With
    
    
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _
        Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select
    ActiveChart.SeriesCollection(1).Trendlines(1).Select
    With Selection.Border
        .ColorIndex = 57
        .Weight = xlHairline
        .LineStyle = xlContinuous
    End With
    With Selection
        .Type = xlLinear
        .Forward = 0
        .Backward = 0
        .InterceptIsAuto = True
        .DisplayEquation = True
        .DisplayRSquared = False
        .NameIsAuto = True
    End With
    ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
    Selection.Left = 152
    Selection.Top = 38
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).Trendlines(1).Select
    With Selection
        .Type = xlLinear
        .Forward = 0
        .Backward = 0
        .InterceptIsAuto = True
        .DisplayEquation = True
        .DisplayRSquared = True
        .NameIsAuto = True
    End With
    
    ActiveChart.Legend.Select
    Selection.Delete
    
GoTo Eind:

  
GrafiekSoiltech:

    Charts.Add
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=Sheets("Blad1").Range("C4"), PlotBy:= _
        xlRows
    ActiveChart.SeriesCollection.NewSeries
    
    ActiveChart.SeriesCollection(1).XValues = "=" & v & "!R3C[" & STXKolom & "]:R[" & LastRow & "]C[" & STXKolom & "]"
    ActiveChart.SeriesCollection(1).Values = "=" & v & "!R3C[" & STYKolom & "]:R[" & LastRow & "]C[" & STYKolom & "]"
    ActiveChart.SeriesCollection(1).Name = "=Blad1!R4C3"
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Blad1"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = Tekst3
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = Tekst1
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = Tekst2
    End With
    With ActiveChart
        .HasAxis(xlCategory, xlPrimary) = True
        .HasAxis(xlValue, xlPrimary) = True
    End With
    ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
    With ActiveChart.Axes(xlCategory)
        .HasMajorGridlines = False
        .HasMinorGridlines = False
    End With
    With ActiveChart.Axes(xlValue)
        .HasMajorGridlines = False
        .HasMinorGridlines = False
    End With
    
    
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _
        Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select
    ActiveChart.SeriesCollection(1).Trendlines(1).Select
    With Selection.Border
        .ColorIndex = 57
        .Weight = xlHairline
        .LineStyle = xlContinuous
    End With
    With Selection
        .Type = xlLinear
        .Forward = 0
        .Backward = 0
        .InterceptIsAuto = True
        .DisplayEquation = True
        .DisplayRSquared = False
        .NameIsAuto = True
    End With
    ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
    Selection.Left = 152
    Selection.Top = 38
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).Trendlines(1).Select
    With Selection
        .Type = xlLinear
        .Forward = 0
        .Backward = 0
        .InterceptIsAuto = True
        .DisplayEquation = True
        .DisplayRSquared = True
        .NameIsAuto = True
    End With
    
    ActiveChart.Legend.Select
      Selection.Delete

Eind:
End Sub

Als de kolommen waaruit de grafiek de gegevens haalt volledig gevuld zijn is er niets aan de hand. Op het moment dat er in de kolom lege cellen voorkomen, dus geen nul maar echt leeg, dan krijg ik de fout melding dat de Eigenschap Values van de klasse Series niet kan worden vastgesteld. Hoe kan ik dit omzeilen of hoe kan ik excel zeggen dat als er lege cellen in een kolom staan dat dit genegeert kan worden.

gr

BB

 

[Breaky623]

Wat wel kan, als er geen value is misschien automatisch op 0 laten zetten ? Zegmaar dat de waarde standaard 0 is.

 

[BasHD]

Fout in grafiek

Hoi Breaky623,

Dat zou kunnen maar het volgende is dan het geval: je krijgt dan allemaal punten met waarde 0 in je grafiek en dat geeft een gek beeld in de trendlijn. Daarom had ik alle zeros gedelete.

gr

Bas