+ Reply to Thread
Results 1 to 1 of 1

Run Time error 1004 in seriescollection(1).pointswhile adding data labels in scatter

  1. #1
    Registered User
    Join Date
    04-20-2016
    Location
    Bangalore
    MS-Off Ver
    2007
    Posts
    3

    Run Time error 1004 in seriescollection(1).pointswhile adding data labels in scatter

    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
    Last edited by gandhiaditya; 11-24-2016 at 01:00 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Adding data labels to a scatter plot
    By mykasd in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-08-2016, 11:24 AM
  2. Adding labels to a scatter chart
    By Jamie100 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-18-2014, 04:01 AM
  3. Adding Labels to Data Points in Scatter Plot Chart
    By Stopea in forum Excel General
    Replies: 10
    Last Post: 05-21-2014, 08:25 PM
  4. [SOLVED] Run-time error 1004 on adding new sheets using unique values
    By Phillips Contracting in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-05-2014, 04:59 PM
  5. SeriesCollection.Values with variable Rows Error 1004
    By generalmokke in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-07-2013, 05:37 AM
  6. Error 1004 on SeriesCollection
    By d_omin in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 08-28-2010, 05:58 AM
  7. Run time error 1004 when adding rows
    By jpruffle in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-28-2009, 07:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1