Below is a code for adding and labeling a scatter plot in excel vba . it is giving a run time error 1004 Invalid Parameter at With.Points(i) highlighted in red.
have been stuck at it for hours now.Any help is appreciated
Option Explicit
Const sizeOfMarker As Integer = 8
Sub OutputSheet_Button1_2_Click()
Dim sheet As Worksheet
Dim sht1 As Worksheet
Set sheet = ThisWorkbook.Worksheets("Output Sheet")
Set sht1 = ThisWorkbook.Worksheets("Input Sheet")
sht1.Unprotect Password:="tlc@12345"
sheet.Unprotect Password:="tlc@12345"
Dim i As Integer
i = 4
Do Until (IsEmpty(sht1.Cells(i, 2).Value))
i = i + 1
Loop
Dim lastrow1 As Integer
Dim acc As Integer
lastrow1 = i - 1
acc = lastrow1 - 3
ReDim labels(1 To acc) As String
For i = 1 To acc
labels(i) = ""
Next i
ReDim pointSymbols(1 To acc) As Integer
For i = 1 To acc
pointSymbols(i) = 0
Next i
ReDim pointColors(1 To acc) As Integer
For i = 1 To acc
pointColors(i) = 0
Next i
For i = 4 To lastrow1
sheet.Cells(i - 2, 4).Value = sht1.Cells(i, 2).Value
sheet.Cells(i - 2, 2).Value = sht1.Cells(i, 13).Value
sheet.Cells(i - 2, 3).Value = sht1.Cells(i, 14).Value
sheet.Cells(i - 2, 5).Value = 1
sheet.Cells(i - 2, 6).Value = 46
Next i
Dim data As Range
Set data = Selection
Dim firstRow As Integer
Dim lastrow As Integer
Dim firstColumn As Integer
Dim lastColumn As Integer
firstRow = 2
firstColumn = 2
i = 2
Do Until (IsEmpty(sheet.Cells(i, 2).Value))
i = i + 1
Loop
lastrow = i - 1
lastColumn = 6
Dim rows As Integer
rows = lastrow - firstRow + 1
Dim columns As Integer
columns = lastColumn - firstColumn + 1
' Identify the range containing the plot data
Dim scatterData As Range
Set scatterData = Range(Cells(firstRow, firstColumn), Cells(firstRow + rows - 1, firstColumn + 1))
' Identify the ranges containing labels, symbols and colors
For i = 2 To lastrow
labels(i - 1) = sheet.Cells(i, firstColumn + 2).Value
pointSymbols(i - 1) = sheet.Cells(i, firstColumn + 3).Value
pointColors(i - 1) = sheet.Cells(i, firstColumn + 4).Value
Next i
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=scatterData
ActiveChart.Location Where:=xlLocationAsObject, Name:=sheet.Name
ActiveChart.HasLegend = False
ActiveChart.PlotArea.Interior.Color = xlNone
' Add and format Gridlines
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
.MajorUnit = 0.5
.MaximumScale = 3
.MinimumScale = 0
End With
With ActiveChart.Axes(xlCategory).MajorGridlines.Delete
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
.MajorUnit = 0.5
.MaximumScale = 3
.MinimumScale = 0
End With
With ActiveChart.Axes(xlValue).MajorGridlines.Delete
End With
With ActiveChart.Parent
.Name = "mychart1"
.Height = Application.InchesToPoints(5.01)
.Width = Application.InchesToPoints(8.19)
End With
' Iterate over each point of the first series:
' add a label, format marker style and color.
With ActiveChart.SeriesCollection(1)
.ApplyDataLabels
For i = 1 To rows
With .Points(i)
.DataLabel.Text = labels(i)
.MarkerStyle = pointSymbols(i)
.MarkerBackgroundColorIndex = pointColors(i)
.MarkerForegroundColorIndex = pointColors(i)
.MarkerSize = sizeOfMarker
End With
Next
End With
sheet.Shapes("mychart1").ZOrder msoSendToBack
sht1.Protect Password:="tlc@12345", DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowInsertingHyperlinks:=True, _
AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
sheet.Protect Password:="tlc@12345", DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowInsertingHyperlinks:=True, _
AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
End Sub
Bookmarks