Hallo liebe Forumsgemeinde,
ich brauche mal wieder dringend Hilfe und hoffe, Ihr könnt mir helfen!
Folgendes:
Habe hier im Forum ein Makro gefunden, mit dem ich ein Diagramm erstellen kann.
Klappt auch alles prima....nun aber ein Problem. In der Tabelle kann es auch vorkommen, dass dort keine Zahlen sind, sondern das Kürzel "ng". Wenn dieses Kürzel in der ersten Zeile steht, dann streikt VBA...steht "ng" in den nachfolgenden Zeilen (und in der ersten Zeile eine Zahl) gibt es keine Probleme...nur in der ersten Zeile gibt es Ärger (egal was in den weiteren Zeilen steht....wie kann ich das Problem beheben?
Wäre riesig, wenn mir jemand einen Tip geben könnte....
Gruß
Olli
Dim numberRows As Variant
Dim Dname As Variant
Dim dname2 As Variant
numberRows = Range("j1").Value
Dname = CStr(pk2)
Dim rangeDef As Variant
Dim zeile As Variant
Dim z
Dim zeile2 As Variant
Dim spalte As Integer
spalte = CSng(bz1) * 6 + 1
UserForm3.av.Caption = Format(Cells(30, spalte), "#,##0.00")
UserForm3.leiste.Caption = Format(Range(bz & 30), "#,##0.00")
UserForm3.nr.Value = bz.Value
UserForm3.person.Caption = UserForm1.Controls("name" & bz1 & "b").Caption
UserForm3.gesamt.Caption = Format(Me.Controls("drp" & bz1).Caption, "#,##0.00")
rangeDef = bz.Value & zeile & " : " & bz.Value & numberRows + zeile2
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets(CStr(pk2)).Range( _
rangeDef), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Name = "gswertung"
ActiveChart.Location Where:=xlLocationAsObject, Name:=Dname
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = False
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = False
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = 15
.MinorUnitIsAuto = True
.MajorUnit = 1
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 57
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Fill.TwoColorGradient Style:=msoGradientFromCenter, Variant:=1
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 36
.Fill.BackColor.SchemeColor = 40
End With
ActiveChart.ChartArea.Select
With Selection.Border
.Weight = 2
.LineStyle = -1
End With
With Selection.Interior
.ColorIndex = 40
.PatternColorIndex = 1
.Pattern = 1
End With
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = " "
.Axes(xlCategory).HasTitle = False
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Characters.Text = " "
End With
With ActiveChart
.HasAxis(xlCategory) = False
.HasAxis(xlValue) = True
End With
ActiveChart.Axes(xlCategory).CategoryType = xlAutomatic
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
.MinimumScale = 0
.MaximumScale = 15
.MinorUnit = 1
End With
ActiveChart.WallsAndGridlines2D = False
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = False

Dim i As Integer
Dim g As Integer
Dim numberRows2 As Variant
numberRows2 = Range("j1").Value
For i = 1 To numberRows2
g = g + 1
If Range("b" & 33 + g).Value = "av" Then
ActiveChart.SeriesCollection(1).Points(i).Interior.ColorIndex = 3
ElseIf Range("b" & 33 + g).Value = "eel" Then
ActiveChart.SeriesCollection(1).Points(i).Interior.ColorIndex = 5
ElseIf Range("b" & 33 + g).Value = "zp" Then
ActiveChart.SeriesCollection(1).Points(i).Interior.ColorIndex = 4
ElseIf Range("b" & 33 + g).Value = "vzp" Then
ActiveChart.SeriesCollection(1).Points(i).Interior.ColorIndex = 7
ElseIf Range("b" & 33 + g).Value = "Wiso" Then
ActiveChart.SeriesCollection(1).Points(i).Interior.ColorIndex = 8
End If
Next i