dynamic select case ?

[Chance1234]

I have two arrays which look like the following;

arrSplit1
arrSplit2

both are identical , have two dimensions and look like the following

AUD,0.4237
BMD,1.6613
BRL,0.4481
BGN,2.4389
CAD,0.9845
CNY,1.116
COP,835.1
DKK,0.415
EGP,9.0769
EUR,0.2666
..
etc

I am at this moment in time only interested in a handful of currencys which are

AUD, EUR, NZD, USD

and from this information, i wish to create FX pairs in the following format.

	AUD/EUR     [AUD]	AUD/NZD     [AUD] etc...	AUD/USD     [AUD]
	Closing	Closing	Closing	
2008/11/06	1.897675	0.881525	1.471839

Which is all working ok, to acheive this im using the following code

...
      For i = 0 To intArrCnt - 1
            Select Case arrSplit1(0, i)

                Case "AUD", "EUR", "NZD", "USD"
                    For y = intFirstR To intArrCnt - 1
                        Select Case arrSplit2(0, y)
                            Case "AUD", "EUR", "NZD", "USD"
                                If arrSplit1(0, i) <> arrSplit2(0, y) Then
                                    strLine1 = strLine1 & vbTab & arrSplit1(0, i) & "/" & arrSplit2(0, y) & "     [" & arrSplit1(0, i) & "]"
                                    strLine2 = strLine2 & vbTab & "Closing"
                                    strLine3 = strLine3 & vbTab & Math.Round(arrSplit1(1, i) / arrSplit1(1, y), 6)
                                End If
                        End Select
                    Next
            End Select
        Next
...

However, the problem I have , is im getting for example

AUD/EUR [AUD]
&
EUR/AUD [EUR]

When i only need one pair for each combination. Is there a way i can dynamically change my select case on each currency iteration ?





Chance,

F, G and Skipper

 

[jebenson]

Hi Chance,

Unfortunately, there's not a way to do a dynamic select case. However, I think this will achieve the results you want:

...

[red]Dim Repeats() As String
Dim UseThis As Boolean[/red]

For i = 0 To intArrCnt - 1
Select Case arrSplit1(0, i)

Case "AUD", "EUR", "NZD", "USD"
For y = intFirstR To intArrCnt - 1
Select Case arrSplit2(0, y)
Case "AUD", "EUR", "NZD", "USD"
If arrSplit1(0, i) <> arrSplit2(0, y) Then
[red]UseThis = False

If Not Repeats Is Nothing Then
If Array.IndexOf(Repeats, arrSplit1(0, i) & "/" & arrSplit2(0, y)) > -1 Then
UseThis = False
Else
UseThis = True
End If
Else
UseThis = True
End If[/red]

[red]If UseThis Then[/red]
strLine1 = strLine1 & vbTab & arrSplit1(0, i) & "/" & arrSplit2(0, y) & " [" & arrSplit1(0, i) & "]"
strLine2 = strLine2 & vbTab & "Closing"
strLine3 = strLine3 & vbTab & Math.Round(arrSplit1(1, i) / arrSplit1(1, y), 6)

[red]If Repeats Is Nothing Then
ReDim Repeats(0)
Else
ReDim Preserve Repeats(Repeats.Length)
End If

Repeats(Repeats.Length - 1) = arrSplit2(0, i) & "/" & arrSplit1(0, y)

End If[/red]

End If
End Select
Next
End Select
Next
...

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!