Making a range into an array?
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Making a range into an array?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    What I would like is instead of having to do this sort of thing:

    B = Array(2,5,7,8,10,12)

    because the array itself could be quite large and so very tedious to write it all in.
    However, I have the numbers 2,5,7,8,10,12 in Range("A1:A6") of my excel worksheet. Ideally I want something like this (but this doesn't work but you should see what I am getting at):

    B= Array(Range("A1:A6"))
    = Array(2,5,7,8,10,12)

    Since in my code I enjoy using the B(0)=2,
    B(3)=8 properties you can have with arrays, but I don't want to type perhaps hundreds of numbers into the array code when there could/should/must be a way of using the values you have in the range.

    Thanks.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I forgot to add, even better if you can take a dynamic range and make it into an array. This would cane !

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Was this message removed?

    Anyway, does anyone know if what I am asking for is possible? It would cane so much if it was.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    to put a range into an array first declare the array as variant:
    mat(x,1) is first column. mat(x,2) second column.

    Sub test()
    Dim mat As Variant
    mat = Range("A1:B6")
    For x = 1 To 6
    Debug.Print mat(x, 1), mat(x,2)
    Next

    'the other way around
    Range("c1:d6") = mat
    End Sub



    regards Tommy

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tommy,

    I want to do this since I want to use the array function property that if

    B = Array (2,5,6,8,10)

    then B(0) = 2, B(1) = 5 etc.

    I have the integers

    2
    5
    6
    8
    10
    like that in column A, [a1:a5] and I am saying that rather than type 2,5,6,8,10 in manually in the code, I rather pick up the values from column A.

    So, if u say mat is an array, then i shoudl be able to use things like

    mat(0) = 2, mat(1) = 5 etc.

    Hope that helps, I think you may be on the right track though, jsut needs to be finished off I think.

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ret79
    You are correct. Mat is an array so you are able to do what you suggested, but it is an array(x,y) where x is for rows and y is for column, so if we use just one column as your example you would fill B(x,1). That is instead of refering to it as B(2) you shall refer to it as B(2,1). If you had 10 columns it would be B(2,10) if you refer to the last column, second read row.

    Sub test()
    Dim B As Variant
    mat = Range("A1:A6")
    For x = 1 To 6
    Debug.Print B(x, 1)
    Next

    'the other way around
    Range("c1:c6") = B
    End Sub





  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sub test()
    Dim a As Integer
    Dim B As Variant
    B = Range("A1:A6")
    For x = 1 To 6
    Debug.Print B(x, 1)
    Next

    'the other way around
    Range("c1:c6") = B

    a = B(4, 1)
    Range("A22") = a *2
    End Sub


    It's just what I needed !!!

    Thanks so much, I am very grateful indeed!

    [ This Message was edited by: RET79 on 2002-03-21 14:24 ]

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Many times when you read values into an array you are reading them from a text file and has an end of file character so you know when the values have stopped. If you have the values in a list in excel you can assume that if you hit a blank space you have come to the last value (or two blank spaces). Here is some code:

    Dim aintNum(0 to 20) as integer
    Dim n as integer

    cells(1,1). select **Selects cell a1
    n=0
    Do while activecell.value<>""
    aintNum(n)=activecell.value
    activecell.offset(1,0).select
    n=n+1
    Loop

    This will loop through all the values and read them into an array. Not that this is NOT a dynamic array, it only has 21 values (0 to 20), you can increase the values by increasing the number from 20 to whatever in the declaration statement. If you want to redimension your array it is slightly more difficult.

    To print this values try this:

    dim p as integer
    For p=0 to n
    cells(p,2).value=aintNum(p)
    Next p

    This will print all your values in b (2nd) column.

    I hope this helps you out.

    Dave

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, that row in the second loop:

    cells(p,1).value needs to be cells(p+1,1).value since the first value of p is 0 and there is no 0 row it would come up with an error unless you use p+1.

    Dave

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Dave, thanks so much I think that's cool.

    Now, I think the next thing that I am gonna try and do is get rid of that constant at the beginning and make it into a dynamic array. I have tried to do this using count, value and stuff but am having trouble with having to declare the thing as a constant.

    Any suggestions anyone?
    Cheers.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com