Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can SUMPRODUCT work on changing arrays? | Excel Discussion (Misc queries) | |||
Updating Automatic links doesn't work with arrays | Excel Discussion (Misc queries) | |||
How to work around arrays... | Excel Programming | |||
Lots of Frustration - Lots of Arrays, Dynamic Ranges Don't Work, Help With Options | Excel Programming | |||
Arrays: querying with two variables, why doesn't it work? | Excel Programming |