Don't understand - Arrays 101 (Help)

[JimMay]

The below macro is bombing 5 rows from (the present) bottom.
I'm sure it is elementary, but obviously not for me.
Can someone point out my problem?
Thanks in Advance..
Jim

Sub Foo()
Dim searchc As String
Dim DestWb As Workbook
Dim MyList As Range
Dim Arr() As Long
Dim i As Integer
Range("B2").Select
searchc = ActiveCell.Offset(0, -1).Value
Set DestWb = Workbooks("Ex1.xls")
Workbooks("Ex2.xls").Activate
Lrow = Cells(Rows.Count, "b").End(xlUp).Row
Set MyList = Range("B2:B" & Lrow)
Range("B2").Select
i = 0
For Each c In MyList
If c.Offset(0, -1) = searchc Then
i = i + 1
Arr(i) = c.Value '' R/T Error 9, Subscript Out of range
c.Offset(1, 0).Select
End If
Next c


End Sub

 

[Dave Peterson]

For Each c In MyList
If c.Offset(0, -1) = searchc Then
i = i + 1
redim preserve arr(1 to i)
Arr(i) = c.Value '' R/T Error 9, Subscript Out of range
'not sure why you're selecting this
c.Offset(1, 0).Select
End If
Next c

 

[JimMay]

Thanks Dave,
Added the line
redim preserve arr(1 to i)
and removed the line
c.Offset(1, 0).Select
Now it works great after I add:
Workbooks("EX1.xls").Activate
Range("B2").Select
Cells(2, 2).Value = Arr(1)
Cells(3, 2).Value = Arr(2)
Cells(4, 2).Value = Arr(3)

Thanks so much,
Jim May

 

[Dave Peterson]

You don't need that Range("B2").select line either.

And unless you know when to stop (which is the last row to be populated), you
might get that same kind of error (subscript out of range). Heck, you may even
miss some of the values in the array.

You could loop through the array:

'since we know that the lbound of arr() is 1 [arr(1 to ###)]
for i = lbound(arr) to ubound(arr)
cells(1+i,2).value = arr(i)
next i

Or you could plop the whole array into that range:

Range("b2").Resize(UBound(arr) - LBound(arr) + 1, 1).Value _
= Application.Transpose(arr)

(Application.transpose() is limited to about 5000 (5417???) elements in xl2k and
before, IIRC).

 

[Guest]

Dave;
Thanks for the additional advice;
I will incorporate this:

for i = lbound(arr) to ubound(arr)
cells(1+i,2).value = arr(i)
next i

into my code - when I get home from work to
my Home PC where I have the WB.

Graciously appreciate your help,
Jim May

You don't need that Range("B2").select line either.

And unless you know when to stop (which is the last row to be populated), you
might get that same kind of error (subscript out of range). Heck, you may even
miss some of the values in the array.

You could loop through the array:

'since we know that the lbound of arr() is 1 [arr(1 to ###)]
for i = lbound(arr) to ubound(arr)
cells(1+i,2).value = arr(i)
next i

Or you could plop the whole array into that range:

Range("b2").Resize(UBound(arr) - LBound(arr) + 1, 1).Value _
= Application.Transpose(arr)

(Application.transpose() is limited to about 5000 (5417???) elements in xl2k and
before, IIRC).

Thanks Dave,
Added the line
redim preserve arr(1 to i)
and removed the line
c.Offset(1, 0).Select
Now it works great after I add:
Workbooks("EX1.xls").Activate
Range("B2").Select
Cells(2, 2).Value = Arr(1)
Cells(3, 2).Value = Arr(2)
Cells(4, 2).Value = Arr(3)

Thanks so much,
Jim May

 

[Dave Peterson]

I like the other one that plops everything at once.

Dave;
Thanks for the additional advice;
I will incorporate this:

for i = lbound(arr) to ubound(arr)
cells(1+i,2).value = arr(i)
next i

into my code - when I get home from work to
my Home PC where I have the WB.

Graciously appreciate your help,
Jim May

You don't need that Range("B2").select line either.

And unless you know when to stop (which is the last row to be populated), you
might get that same kind of error (subscript out of range). Heck, you may even
miss some of the values in the array.

You could loop through the array:

'since we know that the lbound of arr() is 1 [arr(1 to ###)]
for i = lbound(arr) to ubound(arr)
cells(1+i,2).value = arr(i)
next i

Or you could plop the whole array into that range:

Range("b2").Resize(UBound(arr) - LBound(arr) + 1, 1).Value _
= Application.Transpose(arr)

(Application.transpose() is limited to about 5000 (5417???) elements in xl2k and
before, IIRC).

Thanks Dave,
Added the line
redim preserve arr(1 to i)
and removed the line
c.Offset(1, 0).Select
Now it works great after I add:
Workbooks("EX1.xls").Activate
Range("B2").Select
Cells(2, 2).Value = Arr(1)
Cells(3, 2).Value = Arr(2)
Cells(4, 2).Value = Arr(3)

Thanks so much,
Jim May


For Each c In MyList
If c.Offset(0, -1) = searchc Then
i = i + 1
redim preserve arr(1 to i)
Arr(i) = c.Value '' R/T Error 9, Subscript Out of range
'not sure why you're selecting this
c.Offset(1, 0).Select
End If
Next c

JimMay wrote:


The below macro is bombing 5 rows from (the present) bottom.
I'm sure it is elementary, but obviously not for me.
Can someone point out my problem?
Thanks in Advance..
Jim

Sub Foo()
Dim searchc As String
Dim DestWb As Workbook
Dim MyList As Range
Dim Arr() As Long
Dim i As Integer
Range("B2").Select
searchc = ActiveCell.Offset(0, -1).Value
Set DestWb = Workbooks("Ex1.xls")
Workbooks("Ex2.xls").Activate
Lrow = Cells(Rows.Count, "b").End(xlUp).Row
Set MyList = Range("B2:B" & Lrow)
Range("B2").Select
i = 0
For Each c In MyList
If c.Offset(0, -1) = searchc Then
i = i + 1
Arr(i) = c.Value '' R/T Error 9, Subscript Out of range
c.Offset(1, 0).Select
End If
Next c

End Sub