Select Case Alternative

[h20polo137]

I had program that worked well and utilized the select case method. However, from my understanding, the select case will only work with a finite number of choices.

My problem:
Initially, the program gathered log returns based on currencies from a csv file. The program supported 10 currencies, and the select case method was used in order to locate the specific currency, then gather log returns, fx rates, and some other data from this csv file. Each currency has its own 2 worksheets, one with log return data, and another with percentage return data, which is calculated from the 1st worksheet using log return data/fx rates.

However, now i need the program to be flexible based on whatever currencies are supported in the csv file. The updated input file has 12currencies instead of the fixed 10 that the program was written for. I have a summary page in the workbook that has each currency and its corresponding curve used to search for the appropriate returns/fx/etc. I was hoping i could somehow use this list in conjunction with select case method:

for example if i have 12 currencies, the select case doesnt go thru the initial 10, but 12 based on the list.

More than some exact code, im looking for some way to utilize something like the select case method to apply it to a changing number of currencies. Does anybody have any insight as to another method, or if i can still make the select case method work somehow?

Thank you in advance!

Dylan

 

[Scott Huish]

It might help if you could give us some samples of what you are doing exactly with each case

 

[h20polo137]

So the rtag, which is the curve name is taken from a list of 30 or so different currency curves. The problem is, each currency only utilizes only one of the curves, even though some, like USD has 4 different possible curves. So the curve that is associated with each currency is matched from the the 30 or so possible ones using select case. So it looks thru all the possible choices until certain ones are matched, in this case the ones listed in the Select case method. I also have a summary page in the workbook that matches up the currency with the appropriate curve, and this is what im hoping to reference.


As you can see, the ccy curve corresponds to a specific worksheet depending on the currency. This is only a portion of it, i utilize the select case method probably 10 or 12 more times to input different data to the corresponding worksheets. So there are 10 currencies used now, i need to add 2 more, and want to avoid just adding 2 cases everytime i need to alter the corresponding currencies worksheet.

Here is an example of some:

For r = 1 To (FstEmptyRow - 1)

frateval_row = Split(frateval(r), ",")
rtag = frateval_row(0)
ReDim table(Application.Max(UBound(frateval_row), 23) - 1)
j = 0
Select Case rtag
Case "AUD_BBSW_6M_ERS"
Sheets("input.AUD ret").Activate
Sheets("input.AUD ret").Range("C2:Y2").ClearContents
Case "CAD_BA_3M_ERS" ' Import CAD rates:
Sheets("input.CAD ret").Activate
Sheets("input.CAD ret").Range("C2:Y2").ClearContents
Case "CHF_LIBOR_6M_ERS"
Sheets("input.CHF ret").Activate
Sheets("input.CHF ret").Range("C2:Y2").ClearContents
Case "EUR_EURIBOR_6M_ERS"
Sheets("input.EUR ret").Activate
Sheets("input.EUR ret").Range("C2:Y2").ClearContents
Case "GBP_LIBOR_6M_ERS"
Sheets("input.GBP ret").Activate
Sheets("input.GBP ret").Range("C2:Y2").ClearContents
Case "JPY_LIBOR_6M_ERS"
Sheets("input.JPY ret").Activate
Sheets("input.JPY ret").Range("C2:Y2").ClearContents
Case "USD_LIBOR_3M_ERS"
Sheets("input.USD ret").Activate
Sheets("input.USD ret").Range("C2:Y2").ClearContents
Case "DKK_CIBOR_6M_ERS"
Sheets("input.DKK ret").Activate
Sheets("input.DKK ret").Range("C2:Y2").ClearContents
Case "NOK_NIBOR_6M_ERS"
Sheets("input.NOK ret").Activate
Sheets("input.NOK ret").Range("C2:Y2").ClearContents
Case "SEK_STIBOR_3M_ERS"
Sheets("input.SEK ret").Activate
Sheets("input.SEK ret").Range("C2:Y2").ClearContents
End Select

For col = 1 To UBound(frateval_row)
table(j) = frateval_row(col)
j = j + 1
Next col

If UBound(frateval_row) < UBound(table) Then
For j_ = j To UBound(table)
table(j_) = table(j - 1)
Next j_
End If

Select Case rtag
Case "AUD_BBSW_6M_ERS"
Sheets("input.AUD ret").Range("C2:Y2") = table
Case "CAD_BA_3M_ERS"
Sheets("input.CAD ret").Range("C2:Y2") = table
Case "CHF_LIBOR_6M_ERS"
Sheets("input.CHF ret").Range("C2:Y2") = table
Case "EUR_EURIBOR_6M_ERS"
Sheets("input.EUR ret").Range("C2:Y2") = table
Case "GBP_LIBOR_6M_ERS"
Sheets("input.GBP ret").Range("C2:Y2") = table
Case "JPY_LIBOR_6M_ERS"
Sheets("input.JPY ret").Range("C2:Y2") = table
Case "USD_LIBOR_3M_ERS"
Sheets("input.USD ret").Range("C2:Y2") = table
Case "DKK_CIBOR_6M_ERS"
Sheets("input.DKK ret").Range("C2:Y2") = table
Case "NOK_NIBOR_6M_ERS"
Sheets("input.NOK ret").Range("C2:Y2") = table
Case "SEK_STIBOR_3M_ERS"
Sheets("input.SEK ret").Range("C2:Y2") = table
End Select

 

[Jonmo1]

Is it fair to say that the desired sheet is named
"input.xxx ret" where xxx is the left 3 characters of the rtag variable?

Try
Sheets("input." & Left(rtag,3) &" ret").Range("C2:Y2").ClearContents

 

[h20polo137]

thanks for the reply jonmo1.

yes you are correct, in that each of the worksheets is referenced in that nature. However, there are 30 possible rtags, where i only need 12 specific ones. There may be multiple rtags that start with USD_ for example, but i want a specific one. If the currency list goes up to 13, then i would need the program to account for that as well. Hence im trying to find a way to implement something like the select case, but for variable amounts of data, based on a list on the summary page like:

USD = USD_LIBOR_3M
JPY = JPY_LIBOR_6M
....


where each currency i want to observe is associated with ONE specific currency curve.

 

[Jonmo1]

Perhaps

Select Case Ucase(rtag)
    Case "AUD_BBSW_6M_ERS", "CAD_BA_3M_ERS", "CHF_LIBOR_6M_ERS", etc.. '<-all relevant rtag's here.
        Sheets("input." & Left(rtag,3) &" ret").Range("C2:Y2").ClearContents
    Case Else
        'Do nothing
End Select

 

[h20polo137]

ill play around with that and see if that works.

Thanks again for the insight

 

[h20polo137]

I see what you are doing here, and it shortens up the code and makes it nicer, however there is one part that is still bothering me. I would like the rtags to be selected from a list that i specified on the first page of the workbook. Is it possible to make the relevant rtags based on that page?, so that if i add one to the list, or take one away, then it will be automatically added to the Select case options?

Thanks for the help

 

[Jonmo1]

Sure, If you have your list of rtags in say Sheet1 Range A1:A10 for example...

Dim MyRange As Range, x As Variant
Set MyRange = Sheets("Sheet1").Range("A1:A10")
.
.
.
x = Application.Match(rtag, MyRange, 0)
If Not IsError(x) Then
    Sheets("input." & Left(rtag,3) &" ret").Range("C2:Y2").ClearContents
End If
.
.
.

 

[h20polo137]

Thank you so much, the format you provided is working great so far