asked on

Descending Arrays

I am using this code to populate a matrix. All was working well until they decided that they needed to specify the labels as descending or ascending. Example:
1 2 3 4 5
A
B
C
D
E

might need to be
5 4 3 2 1
E
D
C
B
A

easy enough, except the row and column count is not always the same. i thoguht i had this code right, but it is throwing me an error (subscript out of range). i've been looking at it too long, i need some help before my head explodes.

-a

Function PopulateMatrix()
 
Dim aRoman, aRomanD, aAlpha, aAlphaD, aNumD, aPS, aRC
 
'Define arrays
aRoman = Array("I", "II", "III", "IV", "V", "VI")
aRomanD = Array("VI", "V", "IV", "III", "II", "I")
aAlpha = Array("A", "B", "C", "D", "E", "F")
aAlphaD = Array("F", "E", "D", "C", "B", "A")
aNumD = Array(6, 5, 4, 3, 2, 1)
aPS = Array("Prob", "Sev")
aRC = Array("Row", "Col")
    
    'Prevent user from selecting Roman Numerals for both axis
    If Me.Controls("frm" & aPS(0) & "Format") = 1 Then 'Roman Numerals
        If Me.Controls("frm" & aPS(1) & "Format") = 1 Then 'Roman Numerals
        MsgBox "You cannot use Roman Numerals for both Severity and Probability", _
                    vbOKOnly
        Me.Controls("frm" & aPS(0) & "Format") = 3
        End If
    End If
 
'Changes captions to array values correlated with user selection
For j = 0 To 1
    For i = 1 To 6
        Select Case Me.Controls("frm" & Trim(CStr(aPS(j))) & "Format")
            Case Is = 1
                Select Case Me.Controls("frmOrder" & j)
                    Case Is = 1: Me.Controls("lbl" & aPS(j) & i).Caption = aRoman(i - 1)
                    Case Is = 2: Me.Controls("lbl" & aPS(j) & i).Caption = _
                        aRomanD((i - 1) + (6 - Me.Controls("cmb" & aRC(j) & "Qty").Value))
                End Select
            Case Is = 2
                Select Case Me.Controls("frmOrder" & j)
                    Case Is = 1: Me.Controls("lbl" & aPS(j) & i).Caption = i
                    Case Is = 2: Me.Controls("lbl" & aPS(j) & i).Caption = _
                        aNumD((i - 1) + (6 - Me.Controls("cmb" & aRC(j) & "Qty").Value))
                End Select
            Case Is = 3
                Select Case Me.Controls("frmOrder" & j)
                    Case Is = 1: Me.Controls("lbl" & aPS(j) & i).Caption = aAlpha(i - 1)
                    Case Is = 2: Me.Controls("lbl" & aPS(j) & i).Caption = _
                        aAlphaD((i - 1) + (6 - Me.Controls("cmb" & aRC(j) & "Qty").Value))
                End Select
        End Select
    Next
Next
 
'Changes matrix values to concatenation of x & y axis labels
    For i = 1 To 6
      For j = 1 To 6
        Me.Controls("txtP" & i & "s" & j) = Me.Controls("lblSev" & i).Caption & _
                Me.Controls("lblProb" & j).Caption
       Next
    Next
 
End Function

Open in new window

adraughn

ASKER

c'mon dwyer, where are you when i need you?

ASKER CERTIFIED SOLUTION

aesmike

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

adraughn

ASKER

I did finally fix it last night at starbucks, but I am going to compare your code and will let you know if it works.

thanks sweetie.

-a

adraughn

ASKER

This is what I had come up with:

Function PopulateMatrix()

Dim aRoman, aRomanD, aAlpha, aAlphaD, aNumD, aPS, aRC

'Define arrays
aRoman = Array("I", "II", "III", "IV", "V", "VI")
aRomanD = Array("VI", "V", "IV", "III", "II", "I")
aAlpha = Array("A", "B", "C", "D", "E", "F")
aAlphaD = Array("F", "E", "D", "C", "B", "A")
aNumD = Array(6, 5, 4, 3, 2, 1)
aPS = Array("Prob", "Sev")
aRC = Array("Row", "Col")

'Prevent user from selecting Roman Numerals for both axis
If Me.Controls("frm" & aPS(0) & "Format") = 1 Then 'Roman Numerals
If Me.Controls("frm" & aPS(1) & "Format") = 1 Then 'Roman Numerals
MsgBox "You cannot use Roman Numerals for both Severity and Probability", _
vbOKOnly
Me.Controls("frm" & aPS(0) & "Format") = 3
End If
End If

'Changes captions to array values correlated with user selection
For j = 0 To 1
For i = 1 To Me.Controls("cmb" & aRC(j) & "Qty").Value
Select Case Me.Controls("frm" & Trim(CStr(aPS(j))) & "Format")
Case Is = 1
Select Case Me.Controls("frmOrder" & j)
Case Is = 1: Me.Controls("lbl" & aPS(j) & i).Caption = aRoman(i - 1)
Case Is = 2: Me.Controls("lbl" & aPS(j) & i).Caption = _
aRomanD((i - 1) + (6 - Me.Controls("cmb" & aRC(j) & "Qty").Value))
End Select
Case Is = 2
Select Case Me.Controls("frmOrder" & j)
Case Is = 1: Me.Controls("lbl" & aPS(j) & i).Caption = i
Case Is = 2: Me.Controls("lbl" & aPS(j) & i).Caption = _
aNumD((i - 1) + (6 - Me.Controls("cmb" & aRC(j) & "Qty").Value))
End Select
Case Is = 3
Select Case Me.Controls("frmOrder" & j)
Case Is = 1: Me.Controls("lbl" & aPS(j) & i).Caption = aAlpha(i - 1)
Case Is = 2: Me.Controls("lbl" & aPS(j) & i).Caption = _
aAlphaD((i - 1) + (6 - Me.Controls("cmb" & aRC(j) & "Qty").Value))
End Select
End Select
Next
Next

'Changes matrix values to concatenation of x & y axis labels
For i = 1 To 6
For j = 1 To 6
Me.Controls("txtP" & i & "s" & j) = Me.Controls("lblSev" & j).Caption & _
Me.Controls("lblProb" & i).Caption
Next
Next

End Function

adraughn

ASKER

my hero. works great.

-a

adraughn

ASKER

you rock