PDA

View Full Version : Replace non-contiguous column range with Offset column range in Chart SeriesCollectio



sifar786
04-07-2020, 02:44 PM
I have a Clustered Bar Chart that is feeded Values from a B column of a Listobject Table. The A Column is for the XValues. This ListObject may be filtered by a dropdown selection to only show certain visible rows.


On Workbook_SheetChange event, for Sheet1,


If the dropdown selects "Aggr" then the 3rd argument of the SeriesCollection should become,




=SERIES("Aggr",(Sheet1!$A$2:$A$4,Sheet1!$A$6:$A$10),(Sheet1!$B$2:$B$4,Sheet1!$B$6:$B$10), 1)



i.e. select non-contiguous range areas from Column B.


If the dropdown selects "Core" then the 3rd argument of the SeriesCollection should become,




=SERIES("Core",(Sheet1!$A$2:$A$4,Sheet1!$A$6:$A$10),(Sheet1!$D$2:$D$4,Sheet1!$D$6:$D$10), 1)



i.e. select non-contiguous range areas from Column D and vice-versa.


I need a VBA function that can handle contiguous and non-contiguous ranges. I have written the below example which works only for contiguous ranges e.g.




=SERIES("Aggr",Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,1) ' for Aggr



and




=SERIES("Core",Sheet1!$A$2:$A$10,Sheet1!$D$2:$D$10,1) ' for Core



Here is the code which works if the seriesCollection is as shown above for contiguous ranges.




Select Case Target.Value2
Case is = "Aggr" 'selected Aggr in dropdown
For Each Srs In oChart.SeriesCollection
If Srs.Name = "Core" Then
Var = Split(Srs.Formula, ",")
Set Rng = ThisWorkbook.Sheets("Sheet1").Range(Var(2)).Offset(0, -2)
Var(2) = Trim("'" & Mid(Rng.Address(True, True, xlA1, True), InStr(1, Rng.Address(True, True, xlA1, True), "]", vbTextCompare) + 1))
With Srs
.Formula = Join(Var, ",")
.Name = "Aggr"
End With
Exit For
End If
Next Srs
Target.Select


Case is = "Core" 'selected Core in dropdown
For Each Srs In oChart.SeriesCollection
If Srs.Name = "Aggr" Then
Var = Split(Srs.Formula, ",")
Set Rng = ThisWorkbook.Sheets("Sheet1").Range(Var(2)).Offset(0, 2)
Var(2) = Trim("'" & Mid(Rng.Address(True, True, xlA1, True), InStr(1, Rng.Address(True, True, xlA1, True), "]", vbTextCompare) + 1))
With Srs
.Formula = Join(Var, ",")
.Name = "Core"
End With
Exit For
End If
Next Srs
Target.Select
End Select



How can this code be modified to handle 2 or more non-contiguous ranges?