Making a range into an array? - Page 2
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: Making a range into an array?

  1. #11
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    See the post I gave to your other question for the dynamic array part.

    -rh

  2. #12
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-21 15:46, RET79 wrote:
    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.


    Dim rng As Range, B As Variant, c As Long, x As Integer
    Set rng = Range([A1], [A65536].End(xlUp))
    c = rng.Cells.Count
    B = rng
    For x = 1 To c
    Debug.Print B(x, 1)
    Next

    'the other way around
    Range("C1:C" & c) = B



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

    Default

    thanks guys for all your help. I don't know how I ever survived without dynamic arrays!

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

    Default

      
    I have been using these arrays to great effect, however, I am stuck right now witha frustrating problem. Why does the first code work , when the second one does not??

    CODE NO 1.

    Dim B(100, 11) As Variant
    Set rng = Range([A11], [A11].End(xlToRight))

    Let f = Int((rng.Columns.Count - 1) / 3)
    For k = 0 To f Step 1


    i = 0
    For Each x In Array(24, 72, 89, 103, 114)
    B(k, i) = Cells(x, "B").Offset(0, 3 * k).Value
    i = i + 1
    Next x

    For Each x In Array(24)
    B(k, i) = Cells(x, "C").Offset(0, 3 * k).Value
    i = i + 1
    Next x

    For Each x In Array(4, 44, 196, 220)
    B(k, i) = Cells(x, "D").Offset(0, 3 * k).Value
    i = i + 1
    Next x


    Next k

    Range(ActiveCell, ActiveCell.Offset(f, 11)) = B


    CODE NO. 2

    Sub test()
    i = 0
    Dim B(3, 1) As Variant
    For Each x In Array(5, 8, 9)
    B(i, 1) = Cells(x, "C").Value
    i = i + 1
    Next x
    Range("B1:B3") = B
    End Sub


    [ This Message was edited by: RET79 on 2002-03-25 13:00 ]

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