Results 1 to 14 of 14
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Adding Data to an Array (Excel 2K)

    I'm back thinking about arrays again.

    I have an array (that I know the dimensions of) that I want to fill with values in steps.
    like so:
    myArray=Range("CurrentRange").Value
    go do some stuff, move around worksheet, redefine "CurrentRange"
    and then append the new values in CurrentRange to myArray.
    Ultimately when I've collected all the values into the array - I'm going elsewhere in the worksheet and manipulating them and finally dumping them out.

    specific questions
    1) when using <font color=blue>myArray=Range("CurrentRange").Value</font color=blue> how would I specify which column (or columns) in the array that I want filled? All the examples I've found show filling an array all at one time and looping through each value in turn to plug them into the array.
    2)Is this a case for using a Dynamic Array?

    My other strategy would be to create an array for each column and load each individually. But that seems to lack a certain elegance.

    Any suggestions?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Adding Data to an Array (Excel 2K)

    When you fill directly from a range, the array is a 2D array. the first dimension goes from 1 to the num rows, the second goes from 1 to num of columns. To do this myArray variable has to be DIMed as a variant:
    Dim myArray
    It must NOT be declared as an array (dim myArray() even with the correct size)

    Once transferred you can loop through the array to put into another order.

    NOTE: even a single column is a 2D array. 1st being the row number (1 to rows) the second being a 1!

    If i understand correctly,
    I would almost imagine needing a tempArray to just suck in the ranges from the worksheet and then a dynamic array to add new elements to as you proceed.

    Steve

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Adding Data to an Array (Excel 2K)

    Yes, but even with using a temporary Array what is the syntax to add the data to full Array?
    This is where I'm stumped...
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Adding Data to an Array (Excel 2K)

    Not sure exactly what you are asking. If you have a range named CurrentRange, then

    Dim myArray
    myArray=Range("CurrentRange").Value

    will load the range into myArray in one fell swoop. If you want to transfer you will have to loop thru the columns and rows to put them into a dynamic array.

    Could be you be specific on what you are stumped with?

    Steve

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Adding Data to an Array (Excel 2K)

    When I was trying looping - I was using

    For intCounter3 = 1 To intRowCount + 1
    SequenceArray(intCounter3, intCounter2) = Range(ActiveCell.Offset((intCounter3), 0)).Value
    Next intCounter3

    without success....
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Adding Data to an Array (Excel 2K)

    And when I was using a temporary Array to fill the larger array this is the code I was using:

    tempSequenceArray = Range("CurrentRange").Value
    SequenceArray(intCounter2) = tempSequenceArray()

    Where intCounter2 is counting the column.

    no joy there either....
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Adding Data to an Array (Excel 2K)

    Here is an example.
    This routine will create 2 identical arrays. The first loads the array into a variant variable. The second loads into a dynamic array. Note the dynamic array MUST have a size set BEFORE you can load into it.

    Steve

    <pre>Sub ArrayPlay()
    Dim myArray
    Dim myArray2()
    Dim lRow As Long
    Dim lRows As Long
    Dim iCol As Integer
    Dim iCols As Integer
    Dim rng As Range

    Set rng = Range("CurrentRegion")
    lRows = rng.Rows.Count
    iCols = rng.Columns.Count

    ReDim myArray2(1 To lRows, 1 To iCols)
    myArray = rng.Value
    For lRow = 1 To lRows
    For iCol = 1 To iCols
    myArray2(lRow, iCol) = rng.Cells(lRow, iCol).Value
    Next iCol
    Next lRow
    End Sub</pre>


  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Adding Data to an Array (Excel 2K)

    Ah ha!

    it was using rng.Cells vs Range(ActiveCell.Offset((intCounter3), 0)).Value that seems to make a difference.

    not perfect - but I'm on the right track now!

    Tks
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Adding Data to an Array (Excel 2K)

    ActiveCell.Offset((intCounter3), 0).Value

    Should work also (if you want to go that route. Activecell.offset(x,y) is a RANGE object, it is NOT an address.

    I prefer NOT to use activecell in a macro, better to base it some other reference incase the cell pointer is NOT where you think it is. It is also slow to actually select cells in the workbook. You can use them (ie take out values and place values in cells) without selecting anything!

    Steve

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Data to an Array (Excel 2K)

    Just for discussions sake:

    Why append the added or changed items into the array? Loading the entire (changed) range into a variant array is a very efficient process, which should be faster than adding items using a loop and it saves you keeping track of how many items there were and how many items there are to update.

    The other way around: writing an array out to a sheet in one single go is also quite efficient.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Adding Data to an Array (Excel 2K)

    Activecell.offset(x,y) is a RANGE object, it is NOT an address.

    One of those important differences, it drove me back to my reference book for a little more reading. Let's hope I retain the concept!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  12. #12
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Adding Data to an Array (Excel 2K)

    Why append the added or changed items into the array?
    Well I thought it would be easier, because of the way I'm adding info to the worksheet.
    I'll have somewhere between 1-10 columns of data, and I'm inserting a new column to the right of each existing column and adding a formulas. I want to take the results of those formulas and put them in the area, since I'd have the area selected anyway. Getting the (variable) number of non-contiguous columns into an array in one step was something I couldn't visualize doing.

    Is it doable?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Adding Data to an Array (Excel 2K)

    I do NOT think you can read a non-contiguous range DIRECTLY into an array (at least I haven't thought of a way).
    I tried
    naming a non-contiguous range (possible)
    Then used the statement in VB:
    myArray = range(myrange).value
    [where myRange is a non-contiguous range
    myArray became only the "first range" that was in the named range with the appropriate number of columns.

    You can do it INDIRECTLY by looping through the rows and columns.

    It might be faster with large sections of date, to READ the entire CONTINUOUS section into an array, then LOOP through the array cols and rows and pass the values to a separate array, rather than looping through the spreadsheet and reading the values in one at a time. It is an additional step, but I think transferring a RANGE into an array is relatively quick and looping through arrays is quick.

    Fundamentally looping through a 2D array should be similar to looping through the 2d Spreadsheet "table".

    I think the whole looping thru and grabbing the values in the spreadsheet is slow (and would be at least an order of magnitude slower if you actually SELECTED each cell first!), though I haven't experimented with this.

    (If anyone has data or experience the "academic" would like to hear it, my intuition is pretty good, but sometimes wrong).

    Steve

  14. #14
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Adding Data to an Array (Excel 2K)

    Wouldn't you know it, this is exactly what I had to do today - put the values from non-contiguous cells into an array. And Looping through the selection was the only way I could get it done.

    myCounter = 1
    For Each xcell In Selection
    ColourArray(myCounter, 1) = xcell.Value
    myCounter = myCounter + 1
    Next xcell
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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