Don't understand - Arrays 101 (Help)

  • Thread starter Thread starter JimMay
  • Start date Start date
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
 
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
 
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
 
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).
 
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
Click to expand...
Click to expand...
 
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top