Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a ListBox where are some columns names, and I'd like to build a matrix with only the selected columns in it. Is there a simple way to do that?

I'm actually trying with :

Dim Matrix_a() As Variant
J = 0

For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
       J = J + 1
       Matrix_a.Columns(J) = Price.Columns(i)
    End If
Next i

where "Price" is a previously defined range. this doesn't work because of an "incorrect qualificator", but I don't see how to bypass that (my VBA level being quite low).

Am I missing something?

share|improve this question

1 Answer 1

many issues here:

  1. since you have declared Matrix_a as a dynamic array, you need to dimension it before use
  2. you dont need the .Columns reference to Matrix_a
  3. ListBox proerty is Column, not Columns
  4. the ListBox Selected property applies to rows, not columns

Dim'ing an array

'Declaration
Dim Matrix_a() As Variant

' Set a size
Redim Matrix_a(0 to 1, 0 to 1)

' Resize, keeping any existing data intact
Redim Preserve Matrix_a(0 to 1, 0 to 2)

' you can use variables
i = 3
Redim Preserve Matrix_a(0 to 1, 0 to i)

' Note: you can only change the last dimenstion with Preserve
' Having declared (0 to 1, 0 to 1), this won't work:
Redim Preserve Matrix_a(0 to 2, 0 to 1)
share|improve this answer
    
Thanks for your answer, but there are still things I don't understand : 1. How do I ReDim Matrix_a if I don't know exactly how many columns will be selected ? 4. Does it change anything? As i is a number I want to select the ith column from Price. –  Jack Mar 22 '11 at 8:53
    
@Jack added examples of array dim'ing to answer. As to the "...select the i'th column from Price...", how do you plan on selecting columns in the ListBox? Remember that Selected refers to rows –  chris neilsen Mar 22 '11 at 9:19
    
Thanks for your addings, I'm actually doing it like this and it seems to work : 'nRow = UBound(Price) nCol = 0 For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) Then nCol = nCol + 1 End If Next i ReDim Matrix_a(nRow, nCol) nCol = 0 For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) Then nCol = nCol + 1 For r = 1 To nRow Matrix_a(r, nCol) = Price(r, i) Next r End If Next i'. Thanks for all! edit: How do I make the code formating right? –  Jack Mar 22 '11 at 10:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.