+ Reply to Thread
Results 1 to 10 of 10

help assigning elements of dictionary to array and then pasting array into worksheet

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    help assigning elements of dictionary to array and then pasting array into worksheet

    Hi,

    I have a dictionary that has some number of elements in it.

    I assign the elements of the dictionary to a variant array as follows

    Please Login or Register  to view this content.
    the problem is junk2_array then looks like this

    junk2_array(0) = element0
    junk2_array(1) = element1
    junk2_array(2) = element2
    .
    .
    .
    junk2_array(n) = elementn


    now when i try to use this type of assignment into a worksheet

    Please Login or Register  to view this content.

    what happens is the 0th element is pasted into the worksheet from A1 to An.

    Any ideas? I need to paste the large array into a worksheet but because the array is not like (1 to n, 1 to 1) things not work right.

    Need help!

  2. #2
    Forum Contributor
    Join Date
    03-28-2013
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    226

    Re: help assigning elements of dictionary to array and then pasting array into worksheet

    try this
    Please Login or Register  to view this content.
    Last edited by venkatpvc; 12-04-2013 at 11:54 PM.
    Give Feedback and Click(*)

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: help assigning elements of dictionary to array and then pasting array into worksheet

    Try
    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: help assigning elements of dictionary to array and then pasting array into worksheet

    hi, cant use application.transpose because there are more than 65000 elements in the a dictionary.

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: help assigning elements of dictionary to array and then pasting array into worksheet

    Then you need to loop
    e.g
    Please Login or Register  to view this content.
    Last edited by jindon; 12-05-2013 at 07:42 AM.

  6. #6
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: help assigning elements of dictionary to array and then pasting array into worksheet

    hi jindon,

    there is no better way than to "loop".......you're better than me so I guess looping it is........

  7. #7
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: help assigning elements of dictionary to array and then pasting array into worksheet

    my only concern with looping was how long it would take......

  8. #8
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: help assigning elements of dictionary to array and then pasting array into worksheet

    Looping isn't particularly slow when done in memory, it's when you're interacting with the Excel object model it becomes slow i.e writing to a sheet which jindon avoids here. Don't waste time on over optimization

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: help assigning elements of dictionary to array and then pasting array into worksheet

    When you working with Arrays, loop is the basic.

    It sometimes faster than function like Index/Match...etc when dealing with bulky data.

    If you have a toy-like amount of data, anything will be taken.

  10. #10
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: help assigning elements of dictionary to array and then pasting array into worksheet

    thanks! yeah, sometimes its hard not to look for the "best" way to do things......its how you learn........but I agree with you 100% sometimes when you do this you spend WAY to much time on something that you should just "do".

    thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Assigning a 2D Array to a bigger size range results in "#N/A" in the uncovered elements
    By TopXorGuy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-24-2013, 10:45 AM
  2. [SOLVED] Populate Listbox with all rows of a dynamic array where elements of a single array match.
    By Tayque_J_Holmes in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 08-07-2012, 04:54 AM
  3. Problem assigning elements in row to array
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-11-2011, 08:06 AM
  4. Assigning to an array from a worksheet
    By JoWales in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-17-2011, 08:31 AM
  5. pasting array from VBA into worksheet
    By twd000 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-09-2010, 05:36 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1