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?
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?