VBA Loop to fill Chart

[ExcelKid_10]

hello-

I have a simple code that I wrote to loop through data line by line to create a column chart. The code stalls out on the .Values line where I try when I try to add the first series....I get a run-time error '1004'. however, after searching numerous times I cannot find what might be causing it....here is my code

Sub UpdateGraphs_NEW()
Dim LastRow As Long
Dim irow, icol As Long
Dim strSourceSheet As String
Dim strGraphSheet As String

strSourceSheet = "NewGraphData"
strGraphSheet = "Graphs"


'Find the last row of data
Sheets(strSourceSheet).Select
LastRow = Range("o65536").End(xlUp).Row
LastRow = LastRow - 2


Sheets(strGraphSheet).ChartObjects("Chart 4").Activate

For i = 1 To LastRow
irow = i + 2
With ActiveChart.SeriesCollection.NewSeries
.Name = Sheets(strSourceSheet).Cells(irow, 15)
.Values = Sheets(strSourceSheet).Range(Cells(irow, 19), Cells(irow, 24))
.XValues = Sheets(strSourceSheet).Range(Cells(3, 48), Cells(8, 48))
End With
Next i

End Sub

Just looking for someone to point me in the right direction...

thanks!!!
EK

 

[ChrisM]

This problem followed me around forever until I got it straight in my head. Your statement here looks very logical:

.Values = Sheets(strSourceSheet).Range(Cells(irow, 19), Cells(irow, 24))

But you can't use Range and Cells together like that in all cases. When you use Cells, remember it is not a stand-alone entity, it is actually the child of other objects, like Range.Cells or Worksheet.Cells. Just because Excel lets you type it by itself doesn't mean it is not associating itself with something.

Just like Range, if you type Cells with nothing in front of it it will assume you mean the active worksheet.

So, if you are on worksheet A, and your code says: Sheets("B").Range(Cells(1,1)......
you will get an error, because you started with a range on sheet B, and then tried to merge it with cell ranges on sheet A. Make sense?

Anyways, long story short, fully qualify your Cell references and see if that works.

.Values = Range(Sheets(strSourceSheet).Cells(irow, 19), Sheets(strSourceSheet).Cells(irow, 24))

 

[p45cal]

...and of course:

    .XValues = Range(Sheets(strSourceSheet).Cells(3, 48), Sheets(strSourceSheet).Cells(8, 48))

 

[ExcelKid_10]

that worked perfect!! Thanks so much! EK