Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2006

Tip: Looking for answers? Try searching our database.

populate array

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Marina Limeira - 20 Jan 2006 08:21 GMT
How populate array by sheet data ?

example:

a1 = "mary"
a2 = "john"

in VBA

var AR = array( populate from A1 and A2 )

thanks

Marina
Bob Phillips - 20 Jan 2006 08:41 GMT
A couple of ways for you

   ary = Array(Range("A1").Value, Range("A2").Value)

   For Each cell In Selection
       ReDim Preserve ary(i)
       ary(i) = cell.Value
       i = i + 1
   Next cell

both of which produec a single dimension, base 0, array,

OR

   ary = Selection

which creates a 2D, base 1, array

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> How populate array by sheet data ?
>
[quoted text clipped - 10 lines]
>
> Marina
Martin Fishlock - 20 Jan 2006 08:51 GMT
Marina there are at least two was to populate an array depending upon the
variable.

If you use the variant and the array function try:

dim AR as variant
AR = array(activesheet.range("A1:A2")

or the traditional dim array:

dim AR(2) as string ' assuming strings
AR(1)=activesheet.range("A1")
AR(2)=activesheet.range("A2")

or for bigger arrays

dim AR(2) as string ' assuming strings
dim i as long
for i = 1 to 2
  AR(i)=activesheet.cells(i, 1)
next i
Signature


You may need to use the redim statement if you need to change the size.
Check out F1 for help.

HTHs Martin

> How populate array by sheet data ?
>
[quoted text clipped - 10 lines]
>
> Marina
Dave Peterson - 20 Jan 2006 17:55 GMT
Option Explicit
Sub testme02()
 Dim myArr As Variant
 myArr = ActiveSheet.Range("a1:a2").Value
 'myArr = Application.Transpose(ActiveSheet.Range("a1:a2").Value)
End Sub

The first version actually makes a 2 row by 1 column array.

The second (application.transpose version) makes it a single dimension array.

> How populate array by sheet data ?
>
[quoted text clipped - 10 lines]
>
> Marina

Signature

Dave Peterson

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.