PDA

View Full Version : Solved: Filling the array



Kaizer
10-14-2006, 01:55 PM
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?

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

mdmackillop
10-14-2006, 02:17 PM
If you don't use Preserve, you clear the array when you redim


ReDim Preserve Arr(k - 1, 1)

Kaizer
10-14-2006, 02:33 PM
If you don't use Preserve, you clear the array when you redim


ReDim Preserve Arr(k - 1, 1)



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

mdmackillop
10-14-2006, 03:24 PM
Can you post your workbook?

mdmackillop
10-14-2006, 03:32 PM
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
10-14-2006, 10:58 PM
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
10-15-2006, 01:58 AM
Just noticed that if there is only one entry in the array then Transpose doesn't work. How to workaround this one?

mdmackillop
10-15-2006, 02:33 AM
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
10-15-2006, 08:20 AM
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.
... if k>1 then
lstSubDetails.list=Application.Function.Transpose (Arr)
Else
lstSubDetails.AddItem Arr(0,0)
lstSubdetails.AddItem Arr(1,0)
End If

mdmackillop
10-15-2006, 09:52 AM
What I am doing is still filling the list vertically
It is, and I can't see the reason for it.

mdmackillop
10-15-2006, 10:17 AM
Eventually found the solution!

With ListBox1
.AddItem
.List(0, 0) = arr(0, 0)
.List(0, 1) = arr(1, 0)
End With

Kaizer
10-15-2006, 10:27 AM
Thank you mdmckillop. That's what I need.