ActiveChart.SeriesCollection

[Closed]
Report
Posts
2
Registration date
Thursday October 18, 2012
Status
Member
Last seen
October 19, 2012
-
Posts
2
Registration date
Thursday October 18, 2012
Status
Member
Last seen
October 19, 2012
-
Hi, Guys!

Can anyone help me with this code below. I want to make the range of the ActiveChart.SeriesCollection to be from $C$3 to my last cell with values instead of being locked on the $C$12.

Thanks in advance!

Sub Macro3()
'
' Macro3 Macro
'

'
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).XValues = "='FPSO'!$C$3:$C$12"
ActiveChart.SeriesCollection(1).Values = "='FPSO'!$H$3:$H$12"
ActiveChart.SeriesCollection(2).Values = "='FPSO'!$J$3:$J$12"
ActiveChart.SeriesCollection(3).Values = "='FPSO'!$L$3:$L$12"
ActiveChart.SeriesCollection(4).Values = "='FPSO'!$R$3:$R$12"
ActiveChart.SeriesCollection(5).Values = "='FPSO'!$T$3:$T$12"
ActiveChart.SeriesCollection(6).Values = "='FPSO'!$N$3:$N$12"
ActiveChart.SeriesCollection(7).Values = "='FPSO'!$P$3:$P$12"

End Sub
Related:

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
try some thing like this

Sub Macro3()

   Dim lastRow          As Long
   
   lastRow = getItemLocation("*", Sheets("FPSO").Cells)
   If (lastRow < 3) Then Exit Sub
   ActiveSheet.ChartObjects("Chart 4").Activate
   ActiveSheet.ChartObjects("Chart 4").Activate
   With ActiveChart
      .SeriesCollection(1).XValues = "='FPSO'!$C$3:$C$" & lastRow
      .SeriesCollection(1).Values = "='FPSO'!$H$3:$H$" & lastRow
      .SeriesCollection(2).Values = "='FPSO'!$J$3:$J$" & lastRow
      .SeriesCollection(3).Values = "='FPSO'!$L$3:$L$" & lastRow
      .SeriesCollection(4).Values = "='FPSO'!$R$3:$R$" & lastRow
      .SeriesCollection(5).Values = "='FPSO'!$T$3:$T$" & lastRow
      .SeriesCollection(6).Values = "='FPSO'!$N$3:$N$" & lastRow
      .SeriesCollection(7).Values = "='FPSO'!$P$3:$P$" & lastRow
   End With
End Sub

Public Function getItemLocation(sLookFor As String, _
                                rngSearch As Range, _
                                Optional bFullString As Boolean = True, _
                                Optional bLastOccurance As Boolean = True, _
                                Optional bFindRow As Boolean = True) As Long
                                   
   'find the first/last row/column  within a range for a specific string
      
   Dim Cell             As Range
   Dim iLookAt          As Integer
   Dim iSearchDir       As Integer
   Dim iSearchOdr       As Integer
         
   If (bFullString) _
   Then
      iLookAt = xlWhole
   Else
      iLookAt = xlPart
   End If
   If (bLastOccurance) _
   Then
      iSearchDir = xlPrevious
   Else
      iSearchDir = xlNext
   End If
   If Not (bFindRow) _
   Then
      iSearchOdr = xlByColumns
   Else
      iSearchOdr = xlByRows
   End If
         
   With rngSearch
      If (bLastOccurance) _
      Then
         Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
      Else
         Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir)
      End If
   End With
         
   If Cell Is Nothing Then
      getItemLocation = 0
   ElseIf Not (bFindRow) _
   Then
      getItemLocation = Cell.Column
   Else
      getItemLocation = Cell.Row
   End If
   Set Cell = Nothing

End Function

Posts
2
Registration date
Thursday October 18, 2012
Status
Member
Last seen
October 19, 2012

Thanks, rizvisa1!

I'll try this code.