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

[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" <[email protected]> wrote in message
news:[email protected]:

> 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]

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" <[email protected]> wrote in message
> news:[email protected]:
>
> > 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

[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" 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" <[email protected]> wrote in message
> > news:[email protected]:
> >
> > > 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]

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" <[email protected]> wrote in message
> > > news:[email protected]:
> > >
> > > > 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