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

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

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

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" <(E-Mail Removed)> wrote in message
news:6D53B28E-4D14-4DD4-9F30-(E-Mail Removed)...
> 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
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:6D53B28E-4D14-4DD4-9F30-(E-Mail Removed)...
> > 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
> >>
> >>
>
>