Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
array help needed
hi guys i need what seems so simple but i cant figure it out.
i need an array to hold all the values it finds in column be starting from range be6 and continuing down wards untill an empty cell is found and then end the array can anyone help rivers |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
array help needed
Code: -------------------- Dim vArr As Variant vArr = Range("BE6", Range("BE6").End(xlDown)).Value -------------------- -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24240 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
array help needed
sorry but i donot understand how to use this array.
my cell values store spreadsheet names and i wish to store them all in the variable and then call them into a add.item loop can you please explain how i can do this a seriously stuck with your answer "shg" wrote: Code: -------------------- Dim vArr As Variant vArr = Range("BE6", Range("BE6").End(xlDown)).Value -------------------- -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24240 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
array help needed
You can pick up the values in a range this way and build a two dimensional array
(# rows by # columns). In this case, it's # rows by 1 column. You can use this to loop through the values in the array: Dim vArr As Variant Dim iCtr As Long With ActiveSheet vArr = .Range("BE6", .Range("BE6").End(xlDown)).Value End With For iCtr = LBound(vArr, 1) To UBound(vArr, 1) MsgBox vArr(iCtr, 1) Next iCtr Rivers wrote: sorry but i donot understand how to use this array. my cell values store spreadsheet names and i wish to store them all in the variable and then call them into a add.item loop can you please explain how i can do this a seriously stuck with your answer "shg" wrote: Code: -------------------- Dim vArr As Variant vArr = Range("BE6", Range("BE6").End(xlDown)).Value -------------------- -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24240 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
array help needed
You show 'add.item loop' in your latest post... exactly what are you
planning to add your array elements to? While the syntax you posted is incorrect for my guess of a ListBox or ComboBox; however, if that is indeed what you want to populate, you do not need to load up an array first in order to do it. Consider this example for a ListBox on a UserForm (same code for a ComboBox except for the control's name, of course)... Dim LastRow As Long With Worksheets("Sheet1") LastRow = .Cells(.Rows.Count, "BE").End(xlUp).Row Me.ListBox1.RowSource = "BE6:BE" & LastRow End With If my guess was correct and if you have trouble adapting my code, tell us what control you have, where it is located (UserForm or Worksheet) and, if directly on a worksheet, which toolbar you got it from. -- Rick (MVP - Excel) "Rivers" wrote in message ... sorry but i donot understand how to use this array. my cell values store spreadsheet names and i wish to store them all in the variable and then call them into a add.item loop can you please explain how i can do this a seriously stuck with your answer "shg" wrote: Code: -------------------- Dim vArr As Variant vArr = Range("BE6", Range("BE6").End(xlDown)).Value -------------------- -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24240 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
array help needed
Hi,
I think the OP doesn't realize that the one line vArr = Range("BE6", Range("BE6").End(xlDown)).Value loads the entire array in one step. Here is some sample code for the op Sub testArray() Dim myArray As Variant Dim element As Variant myArray = [A1:A10] For Each element In myArray MsgBox element Next element End Sub So you can use a for each loop to work with your array. -- Thanks, Shane Devenshire "Rick Rothstein" wrote: You show 'add.item loop' in your latest post... exactly what are you planning to add your array elements to? While the syntax you posted is incorrect for my guess of a ListBox or ComboBox; however, if that is indeed what you want to populate, you do not need to load up an array first in order to do it. Consider this example for a ListBox on a UserForm (same code for a ComboBox except for the control's name, of course)... Dim LastRow As Long With Worksheets("Sheet1") LastRow = .Cells(.Rows.Count, "BE").End(xlUp).Row Me.ListBox1.RowSource = "BE6:BE" & LastRow End With If my guess was correct and if you have trouble adapting my code, tell us what control you have, where it is located (UserForm or Worksheet) and, if directly on a worksheet, which toolbar you got it from. -- Rick (MVP - Excel) "Rivers" wrote in message ... sorry but i donot understand how to use this array. my cell values store spreadsheet names and i wish to store them all in the variable and then call them into a add.item loop can you please explain how i can do this a seriously stuck with your answer "shg" wrote: Code: -------------------- Dim vArr As Variant vArr = Range("BE6", Range("BE6").End(xlDown)).Value -------------------- -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24240 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
array help needed
Why do you folks quote whole posts? Bandwidth is free, disk space is unlimited, repetition is reinforcement, rereading is rewarding? -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24240 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula Help Needed | Excel Worksheet Functions | |||
Possible Array Solution Needed? | Excel Discussion (Misc queries) | |||
Maybe an array is needed? | Excel Worksheet Functions | |||
Array formula needed | Excel Worksheet Functions | |||
Array Help Needed | Excel Programming |