ActiveChart.SeriesCollection
Closed
leuroyz
leuroyz
- Posts
- 2
- Registration date
- Thursday October 18, 2012
- Status
- Member
- Last seen
- October 19, 2012
leuroyz
- Posts
- 2
- Registration date
- Thursday October 18, 2012
- Status
- Member
- Last seen
- October 19, 2012
Related:
- ActiveChart.SeriesCollection
- Copy ActiveChart properties to cell (from graphics) ✓ - Forum - Excel
1 reply
rizvisa1
Oct 18, 2012 at 05:23 PM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Oct 18, 2012 at 05:23 PM
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
Oct 19, 2012 at 08:44 AM
I'll try this code.