Don't understand - Arrays 101 (Help)

J

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
 
D

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
 
J

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
 
D

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).
 
J

Jim May

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

Dave Peterson said:
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
 
D

Dave Peterson

I like the other one that plops everything at once.

Jim said:
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

Dave Peterson said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top