Solved: Using a loop with Select Case

[greglittle]

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

[magelan]

This is an example of bad coding

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

Youre not creating a string in the Select statement. Instead, you should do something more like this, assuming risk_curve_Ex is a country since that is the case you are selecting.

case Risk_Curve_Ex1, Risk_Curve_Ex2, Risk_Curve_Ex3 , Risk_Curve_Ex4, _
& Risk_Curve_Ex5, Risk_Curve_Ex6, Risk_Curve_Ex7, Risk_Curve_Ex8, Risk_Curve_Ex9, _
& Risk_Curve_Ex10, Risk_Curve_Ex11, Risk_Curve_Ex12, Risk_Curve_Ex13, Risk_Curve_Ex14, _
& Risk_Curve_Ex15, Risk_Curve_Ex16, Risk_Curve_Ex17

[greglittle]

Yeah, that makes sense. Thanks.