Consulting

Results 1 to 9 of 9

Thread: Solved: Write to excel from VBA array

  1. #1
    VBAX Regular
    Joined
    Jan 2012
    Posts
    6
    Location

    Solved: Write to excel from VBA array

    Hi all,

    Just realized my previous post is confusing and the procedure i was envisioning way too complicated - apologies.

    Assuming i have a 2D VBA array with circa 1mio values, how can i write to a1 in excel all the VBA values in successive iterations? Like i was keeping F9 pressed?

    Thank you again.

    Cheers, n

  2. #2
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    I'm not sure what you want.

    You have an array.
    The user presses a button(?)
    and the first value appears in A1.
    Then what happens? Does the user use the rest of the sheet as normal or???

    The user presses the button again and the second value apears in A1, etc.

    Also since you have a 2D array, I assume that myArray(1,1) is the first value.
    Is the second value (according to your counting) myArray(2,1) or myArray(1,2)?

  3. #3
    VBAX Regular
    Joined
    Jan 2012
    Posts
    6
    Location
    Quote Originally Posted by mikerickson
    I'm not sure what you want.

    You have an array.
    The user presses a button(?)
    and the first value appears in A1.
    Then what happens? Does the user use the rest of the sheet as normal or???

    The user presses the button again and the second value apears in A1, etc.

    Also since you have a 2D array, I assume that myArray(1,1) is the first value.
    Is the second value (according to your counting) myArray(2,1) or myArray(1,2)?

  4. #4
    VBAX Regular
    Joined
    Jan 2012
    Posts
    6
    Location
    Hi & thanks for your post.
    What i'm trying to do is as follows:
    - populate a VBA array from lots of data in xls (myarray)
    - write the content of this array in a1 element by element
    - update c1 every time a1 changes
    - populate a VBA array with all the c1 the code will generate (bigarray)
    - write the content of this last array into excel again

    I think i have point 1-3 down, but not sure (i) is set correctly - i.e. Get me all the values c1 wil assume when a1 changes - & keep getting runtime error13 for bigarray(i).

    Any help much appreciated.

    Cheers, n

    [VBA]Sub zurigo
    Dim myarray As Variant
    Dim bigArray As Variant
    myarray = Worksheets("Sheet2").Range("A1:o50")
    For x = 1 To 5
    For j = 1 To 5
    For i = 1 To myarray(x, j)
    Range("a1") = myarray(x, j)
    'bigArray(i) = Range("c1")
    Next
    Next
    Nex
    Range("data").Resize(1, 250) = bigArray
    End Sub[/VBA]

  5. #5
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    I'd use a loop like

    [VBA]Dim i As Long, j As Long
    Dim myArray as Long, myBigArray as Variant

    Rem fill myArray

    myBigArray = myArray

    For i = LBound(myArray,1) To UBound(myArray,1)
    For j = LBound(myArray, 2) To UBound(myArray, 2)
    Range("B1").Value = myArray(i, j)
    myBigArray(i, j) = Range("C1").Value
    Next j
    Next i[/VBA]

  6. #6
    VBAX Regular
    Joined
    Jan 2012
    Posts
    6
    Location
    You did it.
    Much obliged.
    Have a good day,
    Zurigo

    P.s i'd like to tag this post as solved but struggling to find button, etc

  7. #7
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,039
    Location
    Just above your first post is a dropdown "Thread Tools". Select this then scroll down to Mark Thread Solved, select this then click on Perform Action
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  8. #8
    VBAX Regular
    Joined
    Jan 2012
    Posts
    6
    Location
    Done. Thx

  9. #9
    VBAX Newbie
    Joined
    Jun 2012
    Posts
    4
    Location
    good job

Posting Permissions

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