VBA run-time 1004 error

Question

I've been getting a run-time 1004 error on VBA. I know what the problem is, but I just can't find a solution to this.

I'm using Microsoft Excel 2013

The sample data is as follows:

1   01/19/2015  40  0.250006538
                52  0.052997791
                58  0.010990106
2   01/20/2015  40  0.250001126
                52  0.052997369
                58  0.010990412
3   01/21/2015  40  0.250005088
                52  0.052996605
                58  0.010990819

And it goes on and on. What I intend to do is to extract the values of the data beside a certain number (e.g for 40, it'll be 0.250006538, 0.250001126, 0.250005088 etc.) and plot them on a graph.

What i've done so far is to Union the range together as such:

'checks for number 40
For each c In rng1.Cells
        Set cellRange = Range("D" & c.Row)
        Set cCell1 = Union(cCell1, cellRange)
Next

And i've also tried this method:

 'checks for number 40
 For each c In rng1.Cells
    If Not s = vbNullString Then
           s = s & "," & Range("D" & c.Row).address
    Else
           s = Range("D" & c.Row).address
    End If
 Next

The end result i'll get is either a cCell1 variable or s variable with a list of addresses.

 $D$2,$D$5,$D$8,$D$11,$D$14,$D$17,$D$20,$D$23,$D$26,$D$29,$D$32,$D$35,$D$38,$D$41,$D$44,$D$47,$D$50,$D$53,$D$56,$D$59,$D$62,$D$65,$D$68,$D$71,$D$74,$D$77,$D$80,$D$83,$D$86,$D$89,$D$92,$D$95,$D$98,$D$101,$D$104,$D$107,$D$110,$D$113,$D$116,$D$119,$D$122,$D$125,$D$128,$D$131,$D$134,$D$137,$D$140,$D$143,$D$146,$D$149,$D$152,$D$155,$D$158,$D$161,$D$164,$D$167,$D$170,$D$173,$D$176,$D$179,$D$182,$D$185,$D$188,$D$191,$D$194,$D$197,$D$200,$D$203,$D$206,$D$209,$D$212,$D$215,$D$218,$D$221,$D$224,$D$227,$D$230,$D$233,$D$236,$D$239,$D$242

The problem is when I try to plot the graph on a chart with

 ActiveChart.SeriesCollection(1).Values = Range(cCell1.address)

or

 ActiveChart.SeriesCollection(1).Values = Range(s)

It stops and gives me a runtime-error '1004'.

I assume this is happening because the addresses in the variables are too long to be processed by the Range function... The datasheet i'm working with has hundreds to thousands of data, and the graph has to be plotted according to the number of points.

Is there any workarounds to this? Thank you so much!

You have to fullfil all cells with date to be able to create chart/graph instead of creating string of cell adresses. — Maciej Los
Your disregard for any mention of a parent worksheet to the cells holding the chart data troubles me. What is debug.print range(s).address(external:=true) ? — user4039065

Tim Williams Tim Williams · Accepted Answer · 2015-08-26T05:54:36

This worked for me...

Sub Tester()

    Dim c As Range, cTot As Range, s As Series

    'collect all values for "40"
    For Each c In Range("D1:D27").Cells
        If c.Offset(0, -1) = 40 Then
            If cTot Is Nothing Then
                Set cTot = c
            Else
                Set cTot = Application.Union(cTot, c)
            End If
        End If
    Next c

    'create new series
    Set s = ActiveSheet.ChartObjects(1).Chart.SeriesCollection.NewSeries()

    s.Values = cTot  'set values

End Sub

VBA run-time 1004 error

1 Answers