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

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

--

Dave Peterson

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" wrote in message
:

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


--

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



JimMay wrote:

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" wrote in message
:

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


--

Dave Peterson

--

Dave Peterson

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" wrote:

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



JimMay wrote:

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" wrote in message
:

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


--

Dave Peterson

--

Dave Peterson

I like the other one that plops everything at once.

Jim May wrote:

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" wrote:

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



JimMay wrote:

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" wrote in message
:

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


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson