+ Reply to Thread
Results 1 to 4 of 4

Extracting 3 Arrays from 1 Array

  1. #1
    Forum Contributor
    Join Date
    10-03-2004
    Posts
    102

    Extracting 3 Arrays from 1 Array

    I have a 2-dimensional array [vtData(0, 0)] that is Dimensioned as a Variant; its Lbound for each dimension is 0 and its UBound for dimension1 is 4 and its UBound for dimension2 is 2; therefore, 15 elements. (This array is imported into my VBA Code from Bloomberg.)

    I do NOT want to paste this array into a worksheet; however, in design mode only, I am using the following to do exactly that (and it works):

    Please Login or Register  to view this content.
    where UB1 is UBound for dimension1.

    OK, I want to create 3 child arrays (one for each column) from the parent array. (My objective is to connect the individual elements for each column into one string, using a delimiter, and then paste that string into a specified cell in my worksheet.) This is my non-working code:

    Please Login or Register  to view this content.
    When stepping-thru my code:
    when x = 0, there are no errors;
    when x = 1, I get the dreaded 'subscript out of range' error.

    Do I need to use WorksheetFunction.Transpose?

    Thanks for a reply.
    Last edited by Chuckles123; 08-22-2007 at 01:59 PM.

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,645
    Hi there,

    It's a bit difficult to diagnose with only the extract you've posted.

    I can run the following code with no problems:
    Please Login or Register  to view this content.
    The only significant difference between your code & mine is that I've included a Dim statement for vtData - i.e. my code definitely "knows" what vtData is.

    Are you sure that this variant has been declared in your code before the posted extract executes? Obviously it needs to be declared either within the routine itself or at the module or the Public level.

    Hope this is helpful.

    Regards,

    Greg M

  3. #3
    Forum Contributor
    Join Date
    10-03-2004
    Posts
    102
    Thanks Greg, for your prompt reply.

    I should have been more specific in my help request. When I said
    that is Dimensioned as a Variant
    I should have used my Dim stmt, which is
    Please Login or Register  to view this content.
    I tried using your
    Please Login or Register  to view this content.
    Two problems: 1) the number of columns is fixed at 3, but the number of rows is a variable; and 2) I get an error at the following stmt
    Please Login or Register  to view this content.
    (this is where the Bloomberg data is imported into my VBA Code) The Compile error: states "Can't assign to array".
    Obiously, there is more going on here than I am aware.

    Any more ideas?

  4. #4
    Forum Contributor
    Join Date
    10-03-2004
    Posts
    102

    Experiment

    Since I am pulling in my array from Bloomberg, I have simulated the downloaded array; it has the same dimensions. My simulated array is:
    Please Login or Register  to view this content.
    Using the following stmt:
    Please Login or Register  to view this content.
    my code works using the simulated array instead of the one downloaded from Bloomberg; however, this does not solve my problem because I will not know the value of UB1 until the array is downloaded from Bloomberg.

    If I use my original Dim stmt for vtData, I get a "Type mismatch" error on the preceding vtData(x, y) = ... stmt for the simulated array.

    Later

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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