greglittle
10-25-2012, 12:30 PM
I am trying to figure out how to run the i loop under Select Case. I need the loop because the list of reference countries is a dynamic named range (capped at 17) and each value is named Risk_Curve_Ex with a number after it. I get a compile error "Statements and labels invalid between Select Case and first Case."
An help or suggestions on how to rewrite are greatly appreciated.
Sub CountryChange() 'If the Country is changed in the UI, this updates the reference chart
Dim Abrv As String
Dim RefCountry As Variant
Dim Qty As Integer
Dim xVal As Double
Dim yVal As Double
Dim xValMax As Double
Dim yValMax As Double
Qty = Range("Curve_RefCtry_Qty").Value 'The Qty of numbered countries.
Set Ch = Sheet1.ChartObjects("CurveZoom").Chart
Set US = Ch.SeriesCollection(3)
Set RefCtry = Ch.SeriesCollection(2)
Set ProjCtry = Ch.SeriesCollection(4)
Select Case Range("Country").Value '<--------------- SELECT CASE WITH LOOP
For i = 1 To Qty
RefCountry = Range("Risk_Curve_Ex" & i).Value
Case RefCountry
For j = 1 To Qty
RefCtry.Points(j).DataLabel.Font.ColorIndex = 1 'Make Ref Countries Black
Next j
ProjCtry.Points(1).HasDataLabel = False 'Remove label from ProjectCountry series
US.Points(1).DataLabel.Font.ColorIndex = 1 'Make US Black
RefCtry.Points(i).DataLabel.Font.ColorIndex = 3 'Make project country Red on ReferenceCountry series
Next i
An help or suggestions on how to rewrite are greatly appreciated.
Sub CountryChange() 'If the Country is changed in the UI, this updates the reference chart
Dim Abrv As String
Dim RefCountry As Variant
Dim Qty As Integer
Dim xVal As Double
Dim yVal As Double
Dim xValMax As Double
Dim yValMax As Double
Qty = Range("Curve_RefCtry_Qty").Value 'The Qty of numbered countries.
Set Ch = Sheet1.ChartObjects("CurveZoom").Chart
Set US = Ch.SeriesCollection(3)
Set RefCtry = Ch.SeriesCollection(2)
Set ProjCtry = Ch.SeriesCollection(4)
Select Case Range("Country").Value '<--------------- SELECT CASE WITH LOOP
For i = 1 To Qty
RefCountry = Range("Risk_Curve_Ex" & i).Value
Case RefCountry
For j = 1 To Qty
RefCtry.Points(j).DataLabel.Font.ColorIndex = 1 'Make Ref Countries Black
Next j
ProjCtry.Points(1).HasDataLabel = False 'Remove label from ProjectCountry series
US.Points(1).DataLabel.Font.ColorIndex = 1 'Make US Black
RefCtry.Points(i).DataLabel.Font.ColorIndex = 3 'Make project country Red on ReferenceCountry series
Next i