Solved: Filling the array

[Kaizer]

I am trying to fill the array by looping through the range and selecting values by parameter. The code that I am trying does not make the job. Can you help fixing the code, please?

[VBA] For i = 1 To r_Major.Count
If Me.cmbRegion.Value = r_rgn(i).Value And _
Me.cmbPeriod.Value = r_Date(i).Value And _
Me.lstDetails.Value = Rg(i).Value And _
Me.cmbMajor.Value = r_Major(i) And _
Me.cmbMinor.Value = r_Minor(i) Then
k = k + 1
ReDim Arr(k - 1, 1)
Arr(k - 1, 0) = Rg(i)
Arr(k - 1, 1) = r_rgn(i).Offset(0, 1)
End If
Next i
lstSubDetails.List = Arr[/VBA]

[mdmackillop]

If you don't use Preserve, you clear the array when you redim

[vba]
ReDim Preserve Arr(k - 1, 1)

[/vba]

[Kaizer]

If you don't use Preserve, you clear the array when you redim

[vba]
ReDim Preserve Arr(k - 1, 1)

[/vba]

When I use Preserve I get Subscript out of range error and ReDim Preserve Arr(k - 1, 1) is highlighted.

[mdmackillop]

Can you post your workbook?

[mdmackillop]

Hi Kaiser,
I've looked a little more closely.
You cannot redim the first dimension of an array, only the last one. A workaround for this is to set your initial array to (1,100) instead of (100,1) for example, redim it to (1,k-1) and then transpose it for use in filling your listbox. Clear???

[Kaizer]

Hi Kaiser,
I've looked a little more closely.
You cannot redim the first dimension of an array, only the last one. A workaround for this is to set your initial array to (1,100) instead of (100,1) for example, redim it to (1,k-1) and then transpose it for use in filling your listbox. Clear???

Yes, got it. Thanks a lot.

[Kaizer]

Just noticed that if there is only one entry in the array then Transpose doesn't work. How to workaround this one?

[mdmackillop]

Can you simply add an If function if k=1 which presumably will be the case, and just write the result to the listbox using AddItem?

[Kaizer]

Can you simply add an If function if k=1 which presumably will be the case, and just write the result to the listbox using AddItem?

I need help here. How do you tell which Index to use in the list so that it's filled horisontally? What I am doing is still filling the list vertically.
[VBA]... if k>1 then
lstSubDetails.list=Application.Function.Transpose (Arr)
Else
lstSubDetails.AddItem Arr(0,0)
lstSubdetails.AddItem Arr(1,0)
End If
[/VBA]

[mdmackillop]

What I am doing is still filling the list vertically

It is, and I can't see the reason for it.

[mdmackillop]

Eventually found the solution!
[vba]
With ListBox1
.AddItem
.List(0, 0) = arr(0, 0)
.List(0, 1) = arr(1, 0)
End With

[/vba]

[Kaizer]

Thank you mdmckillop. That's what I need.