CODEWITHSUNDEEP
vbaexcelrangeworksheet
charwayne
charwayne

Reputation: 103

Add data series fails when using range

I have the following loop trying to append data series for a range of columns:

Do Until wb.Worksheets("RTS Raw Data").Cells(1, k) = ""
  Set c1 = Cells(3, k)
  Set c2 = Cells(lr, k)
  ActiveChart.SeriesCollection.Add Source:=wb.Worksheets("RTS Raw Data").Range(Cells(3, k), Cells(lr, k))
  ActiveChart.SeriesCollection(11 + k).XValues = wb.Worksheets("RTS Raw Data").Range("B3:B" & lr)
  ActiveChart.SeriesCollection(11 + k).Name = wb.Worksheets("RTS Raw Data").Cells(2, k)

  If (wb.Worksheets("RTS Raw Data").Cells(3, k) = "-999") Then
    mcwb.Shapes("CheckBox" & 8 + k).TextFrame.Characters.Text = "Unused"
  Else
    mcwb.Shapes("CheckBox" & 8 + k).TextFrame.Characters.Text = wb.Worksheets("RTS Raw Data").Cells(2, k)
  End If

  ActiveChart.SeriesCollection(11 + k).AxisGroup = xlPrimary

  k = k + 1
Loop

The code fails on ActiveChart.SeriesCollection.Add Source:=wb.Worksheets("RTS Raw Data").Range(Cells(3, k), Cells(lr, k)) but does not fail when .Range("E3:E" & lr) is used.

Thanks in advance.

Upvotes: 0

Views: 91

Answers (2)

Shai Rado
Shai Rado

Reputation: 33692

In order to have all objects (Range and Cells) qualified with the "RTS Raw Data" sheet, use With wb.Worksheets("RTS Raw Data") , and nest all objects underneath with a . (the code also much clearer to the eye, and shorter).

Also, if you define Dim Ser As Series, it will also help you a lot to code and debug it later.

Try the code below:

Dim Ser As Series

With wb.Worksheets("RTS Raw Data")
    Do Until .Cells(1, k) = ""
        Set c1 = .Cells(3, k)
        Set c2 = .Cells(lr, k)

        Set Ser = ActiveChart.SeriesCollection.Add ' <-- set Series to a variable, easier to code and debug later
        Ser.Values = .Range(.Cells(3, k), .Cells(lr, k))
        Ser.XValues = .Range("B3:B" & lr)
        Ser.Name = .Cells(2, k)

        If .Cells(3, k) = "-999" Then
            mcwb.Shapes("CheckBox" & 8 + k).TextFrame.Characters.Text = "Unused"
        Else
            mcwb.Shapes("CheckBox" & 8 + k).TextFrame.Characters.Text = .Cells(2, k)
        End If

        Ser.AxisGroup = xlPrimary
        k = k + 1
    Loop
End With

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19847

It looks like the ActiveChart isn't on RTS Raw Data.

In your code the Range is defined as being on RTS Raw Data, but the two occurrences of Cells isn't qualified to a sheet so it's using the activesheet.
ActiveChart.SeriesCollection.Add Source:=wb.Worksheets("RTS Raw Data").Range(Cells(3, k), Cells(lr, k))

Try using a With...End With block to shorten your code a bit: 

With wb.Worksheets("RTS Raw Data")
    ActiveChart.SeriesCollection.Add Source:=.Range(.Cells(3, k), .Cells(lr, k))
End With

This is the same as writing: 

 ActiveChart.SeriesCollection.Add Source:=wb.Worksheets("RTS Raw Data").Range(wb.Worksheets("RTS Raw Data").Cells(3, k), wb.Worksheets("RTS Raw Data").Cells(lr, k))

https://msdn.microsoft.com/en-us/library/wc500chb.aspx

Upvotes: 1

Related Questions