Arrays [Why does this not work?]
 

Dim count ' counter
Dim people() As String 'array

'find how many people listed in sheet 2 column A
Sheets("Sheet2").Select
Range("a1").Select
Selection.CurrentRegion.Select
row_count = Selection.Rows.count - 1 'Subtract header

Max = row_count 'array is this big
ReDim people(1 To Max) 'redim array

For count = 1 To Max
While Not IsEmpty(ActiveCell)
count = count + 1
ActiveCell.Offset(1, 0).Select
people(i) = ActiveCell.Value
Wend
Next count

Arrays [Why does this not work?]
 

don't know what else you want to accomplish, but this may work for you:

Sub test()
Dim arr As Variant
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
arr = ws.Range("A2:A" & lastrow)

MsgBox UBound(arr)
End Sub

--


Gary


wrote in message
ups.com...
Dim count ' counter
Dim people() As String 'array

'find how many people listed in sheet 2 column A
Sheets("Sheet2").Select
Range("a1").Select
Selection.CurrentRegion.Select
row_count = Selection.Rows.count - 1 'Subtract header

Max = row_count 'array is this big
ReDim people(1 To Max) 'redim array

For count = 1 To Max
While Not IsEmpty(ActiveCell)
count = count + 1
ActiveCell.Offset(1, 0).Select
people(i) = ActiveCell.Value
Wend
Next count

Arrays [Why does this not work?]
 

Thank you for the help. Thats running correctly now

I was attempting to create an array filled with a list of names( list
can get bigger or smaller)
Then I am going to be passing the names through an autofilter to parse
the list

I got the parsing section working but have had no luck on getting data
into an array

On Sep 12, 2:43 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
don't know what else you want to accomplish, but this may work for you:

Sub test()
Dim arr As Variant
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
arr = ws.Range("A2:A" & lastrow)

MsgBox UBound(arr)
End Sub

--

Gary

wrote in message

ups.com...

Dim count ' counter
Dim people() As String 'array

'find how many people listed in sheet 2 column A
Sheets("Sheet2").Select
Range("a1").Select
Selection.CurrentRegion.Select
row_count = Selection.Rows.count - 1 'Subtract header

Max = row_count 'array is this big
ReDim people(1 To Max) 'redim array

For count = 1 To Max
While Not IsEmpty(ActiveCell)
count = count + 1
ActiveCell.Offset(1, 0).Select
people(i) = ActiveCell.Value
Wend
Next count

Arrays [Why does this not work?]
 

You're looping within your "for/next" loop.

I wouldn't use a variable named count, either. It looks too much like the
..count property. It may not confuse excel, but it would confuse me:

Option Explicit
Sub testme()


Dim myCount ' counter
Dim people() As String 'array
Dim Row_Count As Long
Dim Max As Long
Dim i As Long

'find how many people listed in sheet 2 column A
Sheets("Sheet2").Select
Range("a1").Select
Selection.CurrentRegion.Select
Row_Count = Selection.Rows.count - 1 'Subtract header

Max = Row_Count 'array is this big
ReDim people(1 To Max) 'redim array
i = 0
For myCount = 1 To Max
If IsEmpty(ActiveCell.Value) Then
'skip it
Else
i = i + 1
people(i) = ActiveCell.Value
End If
ActiveCell.Offset(1, 0).Select
Next myCount

If i = 0 Then
MsgBox "no cells added"
Else
ReDim Preserve people(1 To i)
End If

End Sub

wrote:

Dim count ' counter
Dim people() As String 'array

'find how many people listed in sheet 2 column A
Sheets("Sheet2").Select
Range("a1").Select
Selection.CurrentRegion.Select
row_count = Selection.Rows.count - 1 'Subtract header

Max = row_count 'array is this big
ReDim people(1 To Max) 'redim array

For count = 1 To Max
While Not IsEmpty(ActiveCell)
count = count + 1
ActiveCell.Offset(1, 0).Select
people(i) = ActiveCell.Value
Wend
Next count

--

Dave Peterson

Arrays [Why does this not work?]
 

If you want a list of unique names, you could use data|filter|advanced (on
another sheet???).

Or John Walkenbach shows how to loop through a range to get the unique values
he
http://j-walk.com/ss/excel/tips/tip47.htm



" wrote:

Thank you for the help. Thats running correctly now

I was attempting to create an array filled with a list of names( list
can get bigger or smaller)
Then I am going to be passing the names through an autofilter to parse
the list

I got the parsing section working but have had no luck on getting data
into an array

On Sep 12, 2:43 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
don't know what else you want to accomplish, but this may work for you:

Sub test()
Dim arr As Variant
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
arr = ws.Range("A2:A" & lastrow)

MsgBox UBound(arr)
End Sub

--

Gary

wrote in message

ups.com...

Dim count ' counter
Dim people() As String 'array

'find how many people listed in sheet 2 column A
Sheets("Sheet2").Select
Range("a1").Select
Selection.CurrentRegion.Select
row_count = Selection.Rows.count - 1 'Subtract header

Max = row_count 'array is this big
ReDim people(1 To Max) 'redim array

For count = 1 To Max
While Not IsEmpty(ActiveCell)
count = count + 1
ActiveCell.Offset(1, 0).Select
people(i) = ActiveCell.Value
Wend
Next count

--

Dave Peterson

Arrays [Why does this not work?]
 

wouldn't this variation of my original post give a list of unique names?

Sub test()
Dim unique_names As New Collection
Dim lastrow As Long
Dim ws As Worksheet
Dim i As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next

For i = 2 To lastrow
unique_names.Add ws.Range("A" & i), CStr(ws.Range("A" & i))
Next

On Error GoTo 0
MsgBox unique_names.Count
End Sub

--


Gary


"Dave Peterson" wrote in message
...
If you want a list of unique names, you could use data|filter|advanced (on
another sheet???).

Or John Walkenbach shows how to loop through a range to get the unique values
he
http://j-walk.com/ss/excel/tips/tip47.htm



" wrote:

Thank you for the help. Thats running correctly now

I was attempting to create an array filled with a list of names( list
can get bigger or smaller)
Then I am going to be passing the names through an autofilter to parse
the list

I got the parsing section working but have had no luck on getting data
into an array

On Sep 12, 2:43 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
don't know what else you want to accomplish, but this may work for you:

Sub test()
Dim arr As Variant
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
arr = ws.Range("A2:A" & lastrow)

MsgBox UBound(arr)
End Sub

--

Gary

wrote in message

ups.com...

Dim count ' counter
Dim people() As String 'array

'find how many people listed in sheet 2 column A
Sheets("Sheet2").Select
Range("a1").Select
Selection.CurrentRegion.Select
row_count = Selection.Rows.count - 1 'Subtract header

Max = row_count 'array is this big
ReDim people(1 To Max) 'redim array

For count = 1 To Max
While Not IsEmpty(ActiveCell)
count = count + 1
ActiveCell.Offset(1, 0).Select
people(i) = ActiveCell.Value
Wend
Next count

--

Dave Peterson

Arrays [Why does this not work?]
 

Thank you so much thats exactly what I was attempting to do

On Sep 12, 2:59 pm, Dave Peterson wrote:
You're looping within your "for/next" loop.

I wouldn't use a variable named count, either. It looks too much like the
.count property. It may not confuse excel, but it would confuse me:

Option Explicit
Sub testme()

Dim myCount ' counter
Dim people() As String 'array
Dim Row_Count As Long
Dim Max As Long
Dim i As Long

'find how many people listed in sheet 2 column A
Sheets("Sheet2").Select
Range("a1").Select
Selection.CurrentRegion.Select
Row_Count = Selection.Rows.count - 1 'Subtract header

Max = Row_Count 'array is this big
ReDim people(1 To Max) 'redim array
i = 0
For myCount = 1 To Max
If IsEmpty(ActiveCell.Value) Then
'skip it
Else
i = i + 1
people(i) = ActiveCell.Value
End If
ActiveCell.Offset(1, 0).Select
Next myCount

If i = 0 Then
MsgBox "no cells added"
Else
ReDim Preserve people(1 To i)
End If

End Sub



wrote:

Dim count ' counter
Dim people() As String 'array

'find how many people listed in sheet 2 column A
Sheets("Sheet2").Select
Range("a1").Select
Selection.CurrentRegion.Select
row_count = Selection.Rows.count - 1 'Subtract header

Max = row_count 'array is this big
ReDim people(1 To Max) 'redim array

For count = 1 To Max
While Not IsEmpty(ActiveCell)
count = count + 1
ActiveCell.Offset(1, 0).Select
people(i) = ActiveCell.Value
Wend
Next count

--

Dave Peterson

Arrays [Why does this not work?]
 

This should do the trick:

Sub test()
Dim people() As String 'array
Dim Max As Long
Dim i As Integer

'find how many people listed in sheet 2 column A
'Sheets("Sheet1").Select
'Range("a1").Select
'Selection.CurrentRegion.Select
Max = Sheet1.Cells(Rows.count, 1).End(xlUp).Row - 1 'Subtract header
'array is this big
ReDim people(1 To Max) 'redim array
For i = 1 To Max
people(i) = Cells(i + 1, 1)
Next i

'this will put the array back in col C
For i = 1 To Max
Range("C" & i + 1).Value = people(i)
Next
End Sub

Mike F
wrote in message
ups.com...
Dim count ' counter
Dim people() As String 'array

'find how many people listed in sheet 2 column A
Sheets("Sheet2").Select
Range("a1").Select
Selection.CurrentRegion.Select
row_count = Selection.Rows.count - 1 'Subtract header

Max = row_count 'array is this big
ReDim people(1 To Max) 'redim array

For count = 1 To Max
While Not IsEmpty(ActiveCell)
count = count + 1
ActiveCell.Offset(1, 0).Select
people(i) = ActiveCell.Value
Wend
Next count

Arrays [Why does this not work?]
 

Probably (I didn't test, but it looks very much like John Walkenbach's example
to me).

But you may not want to do this:
Dim unique_names As New Collection

Chip Pearson explains why:
http://www.cpearson.com/excel/variables.htm
Look for:
Don't Use The New Keyword In A Dim Statement



Gary Keramidas wrote:

wouldn't this variation of my original post give a list of unique names?

Sub test()
Dim unique_names As New Collection
Dim lastrow As Long
Dim ws As Worksheet
Dim i As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next

For i = 2 To lastrow
unique_names.Add ws.Range("A" & i), CStr(ws.Range("A" & i))
Next

On Error GoTo 0
MsgBox unique_names.Count
End Sub

--

Gary

"Dave Peterson" wrote in message
...
If you want a list of unique names, you could use data|filter|advanced (on
another sheet???).

Or John Walkenbach shows how to loop through a range to get the unique values
he
http://j-walk.com/ss/excel/tips/tip47.htm



" wrote:

Thank you for the help. Thats running correctly now

I was attempting to create an array filled with a list of names( list
can get bigger or smaller)
Then I am going to be passing the names through an autofilter to parse
the list

I got the parsing section working but have had no luck on getting data
into an array

On Sep 12, 2:43 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
don't know what else you want to accomplish, but this may work for you:

Sub test()
Dim arr As Variant
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
arr = ws.Range("A2:A" & lastrow)

MsgBox UBound(arr)
End Sub

--

Gary

wrote in message

ups.com...

Dim count ' counter
Dim people() As String 'array

'find how many people listed in sheet 2 column A
Sheets("Sheet2").Select
Range("a1").Select
Selection.CurrentRegion.Select
row_count = Selection.Rows.count - 1 'Subtract header

Max = row_count 'array is this big
ReDim people(1 To Max) 'redim array

For count = 1 To Max
While Not IsEmpty(ActiveCell)
count = count + 1
ActiveCell.Offset(1, 0).Select
people(i) = ActiveCell.Value
Wend
Next count

--

Dave Peterson

--

Dave Peterson

Arrays [Why does this not work?]
 

so, is he saying to use something like this?

Dim unique_names As Collection
Set unique_names = New Collection


--


Gary


"Dave Peterson" wrote in message
...
Probably (I didn't test, but it looks very much like John Walkenbach's example
to me).

But you may not want to do this:
Dim unique_names As New Collection

Chip Pearson explains why:
http://www.cpearson.com/excel/variables.htm
Look for:
Don't Use The New Keyword In A Dim Statement



Gary Keramidas wrote:

wouldn't this variation of my original post give a list of unique names?

Sub test()
Dim unique_names As New Collection
Dim lastrow As Long
Dim ws As Worksheet
Dim i As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next

For i = 2 To lastrow
unique_names.Add ws.Range("A" & i), CStr(ws.Range("A" & i))
Next

On Error GoTo 0
MsgBox unique_names.Count
End Sub

--

Gary

"Dave Peterson" wrote in message
...
If you want a list of unique names, you could use data|filter|advanced (on
another sheet???).

Or John Walkenbach shows how to loop through a range to get the unique
values
he
http://j-walk.com/ss/excel/tips/tip47.htm



" wrote:

Thank you for the help. Thats running correctly now

I was attempting to create an array filled with a list of names( list
can get bigger or smaller)
Then I am going to be passing the names through an autofilter to parse
the list

I got the parsing section working but have had no luck on getting data
into an array

On Sep 12, 2:43 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
don't know what else you want to accomplish, but this may work for you:

Sub test()
Dim arr As Variant
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
arr = ws.Range("A2:A" & lastrow)

MsgBox UBound(arr)
End Sub

--

Gary

wrote in message

ups.com...

Dim count ' counter
Dim people() As String 'array

'find how many people listed in sheet 2 column A
Sheets("Sheet2").Select
Range("a1").Select
Selection.CurrentRegion.Select
row_count = Selection.Rows.count - 1 'Subtract header

Max = row_count 'array is this big
ReDim people(1 To Max) 'redim array

For count = 1 To Max
While Not IsEmpty(ActiveCell)
count = count + 1
ActiveCell.Offset(1, 0).Select
people(i) = ActiveCell.Value
Wend
Next count

--

Dave Peterson

--

Dave Peterson

Arrays [Why does this not work?]
 

Yep.

Gary Keramidas wrote:

so, is he saying to use something like this?

Dim unique_names As Collection
Set unique_names = New Collection

--

Gary

"Dave Peterson" wrote in message
...
Probably (I didn't test, but it looks very much like John Walkenbach's example
to me).

But you may not want to do this:
Dim unique_names As New Collection

Chip Pearson explains why:
http://www.cpearson.com/excel/variables.htm
Look for:
Don't Use The New Keyword In A Dim Statement



Gary Keramidas wrote:

wouldn't this variation of my original post give a list of unique names?

Sub test()
Dim unique_names As New Collection
Dim lastrow As Long
Dim ws As Worksheet
Dim i As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next

For i = 2 To lastrow
unique_names.Add ws.Range("A" & i), CStr(ws.Range("A" & i))
Next

On Error GoTo 0
MsgBox unique_names.Count
End Sub

--

Gary

"Dave Peterson" wrote in message
...
If you want a list of unique names, you could use data|filter|advanced (on
another sheet???).

Or John Walkenbach shows how to loop through a range to get the unique
values
he
http://j-walk.com/ss/excel/tips/tip47.htm



" wrote:

Thank you for the help. Thats running correctly now

I was attempting to create an array filled with a list of names( list
can get bigger or smaller)
Then I am going to be passing the names through an autofilter to parse
the list

I got the parsing section working but have had no luck on getting data
into an array

On Sep 12, 2:43 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
don't know what else you want to accomplish, but this may work for you:

Sub test()
Dim arr As Variant
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
arr = ws.Range("A2:A" & lastrow)

MsgBox UBound(arr)
End Sub

--

Gary

wrote in message

ups.com...

Dim count ' counter
Dim people() As String 'array

'find how many people listed in sheet 2 column A
Sheets("Sheet2").Select
Range("a1").Select
Selection.CurrentRegion.Select
row_count = Selection.Rows.count - 1 'Subtract header

Max = row_count 'array is this big
ReDim people(1 To Max) 'redim array

For count = 1 To Max
While Not IsEmpty(ActiveCell)
count = count + 1
ActiveCell.Offset(1, 0).Select
people(i) = ActiveCell.Value
Wend
Next count

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Arrays [Why does this not work?]
 

ok, thanks dave.

--


Gary


"Dave Peterson" wrote in message
...
Yep.

Gary Keramidas wrote:

so, is he saying to use something like this?

Dim unique_names As Collection
Set unique_names = New Collection

--

Gary

"Dave Peterson" wrote in message
...
Probably (I didn't test, but it looks very much like John Walkenbach's
example
to me).

But you may not want to do this:
Dim unique_names As New Collection

Chip Pearson explains why:
http://www.cpearson.com/excel/variables.htm
Look for:
Don't Use The New Keyword In A Dim Statement



Gary Keramidas wrote:

wouldn't this variation of my original post give a list of unique names?

Sub test()
Dim unique_names As New Collection
Dim lastrow As Long
Dim ws As Worksheet
Dim i As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next

For i = 2 To lastrow
unique_names.Add ws.Range("A" & i), CStr(ws.Range("A" & i))
Next

On Error GoTo 0
MsgBox unique_names.Count
End Sub

--

Gary

"Dave Peterson" wrote in message
...
If you want a list of unique names, you could use data|filter|advanced
(on
another sheet???).

Or John Walkenbach shows how to loop through a range to get the unique
values
he
http://j-walk.com/ss/excel/tips/tip47.htm



" wrote:

Thank you for the help. Thats running correctly now

I was attempting to create an array filled with a list of names( list
can get bigger or smaller)
Then I am going to be passing the names through an autofilter to parse
the list

I got the parsing section working but have had no luck on getting data
into an array

On Sep 12, 2:43 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
don't know what else you want to accomplish, but this may work for
you:

Sub test()
Dim arr As Variant
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
arr = ws.Range("A2:A" & lastrow)

MsgBox UBound(arr)
End Sub

--

Gary

wrote in message

ups.com...

Dim count ' counter
Dim people() As String 'array

'find how many people listed in sheet 2 column A
Sheets("Sheet2").Select
Range("a1").Select
Selection.CurrentRegion.Select
row_count = Selection.Rows.count - 1 'Subtract header

Max = row_count 'array is this big
ReDim people(1 To Max) 'redim array

For count = 1 To Max
While Not IsEmpty(ActiveCell)
count = count + 1
ActiveCell.Offset(1, 0).Select
people(i) = ActiveCell.Value
Wend
Next count

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson