Results 1 to 6 of 6

Thread: Importing range into 2d array?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2010
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Importing range into 2d array?

    Hello all. Is there a way to import a range into a 2d array, without having to use a loop. Ive read that you can create an array from a range using
    Code:
    array=range("A1:A10").resize(1,10)
    but Im not sure how to do that for just one row of a 2d array.

    I have a 2D array with 2 rows and 10 columns. I want the first row (array(1,1) to array (1,10)) to be data from a workbook range. Then the second row will be data resulting from calculations done to row 1. I've tried using the above code to load the range into the array, but it only changes the value of array(1,10).

  2. #2
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Importing range into 2d array?

    Do you want to do the calculations in VBA *after* the first part has been loaded?

    You could potentially load the whole range, then loop through columns 2 to 7 (in this example) and set those values to 0. Something like:

    Code:
    Sub LoadArray()
        Dim arData()
        Dim Rng As Range
        Dim i As Integer
        Dim j As Integer
     
        Set Rng = Range("A1").CurrentRegion
        arData = Rng.Value
     
        For i = LBound(arData) To UBound(arData)
            For j = 2 To 7
                arData(i, j) = 0
            Next j
        Next i
     
        Range("A50").Resize(UBound(arData), 7).Value = arData
    End Sub
    Note:
    When you load an array from a worksheet it's 1-based, not 0-based.

    The above code loads the data in the table containing A1, places 0 values in all but the first column, and then reads the data back to the worksheet as a check.

    Denis
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  3. #3
    New Member
    Join Date
    Jun 2010
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Importing range into 2d array?

    I dont quite follow what you're saying.

    Yes, I do want to first load the data in the array then perform calculations. I suppose I could make two separate arrays, I was planning on keeping it as the same one for convenience.

    Basically I wanted to have a 2x10 array. The first "row" of the array (locations 1,1 through 1,10) would be values from a range, say A1:A10. I want to load that all at once, since the range will exist prior to loading the array. After the data is in the array, I want to perform calculations, say array(1,1) + 1, array(1,2)+2 and so on. These values would be stored in array(2,1) to array (2,10). Does that make sense?
    Last edited by GLIBBY; Nov 6th, 2011 at 09:46 PM.

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Importing range into 2d array?

    Hello,

    Something like this perhaps?

    Code:
    Sub foo()
    Dim varArr() As Variant
    Dim i As Long, j As Long
    Let varArr = Range("A1:A10").Value
    Let j = UBound(varArr, 1)
    ReDim Preserve varArr(1 To j, 1 To j)
    For i = LBound(varArr, 1) To j
        Let varArr(i, 2) = varArr(i, 1) * 2
        Debug.Print varArr(i, 1), varArr(i, 2)
    Next
    End Sub
    Last edited by NateO; Nov 6th, 2011 at 10:08 PM.

  5. #5
    New Member
    Join Date
    Jun 2010
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Importing range into 2d array?

    There must be some problem with how Im defining my range. I tried adapting your code for my application and I get a type mismatch. My actual range is defined as:

    Code:
    Dim a() As Variant
            
        
        Worksheets("Sheet1").Select
        Let a = Range("C1263, I1263, O1263, U1263, AA1263, AG1263, AM1263, AS1263, AY1263, BE1263").Value
    Not quite sure what Im doing wrong here?

  6. #6
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,453
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Importing range into 2d array?

    If your range is not contiguous I don't think you can use a variant to create a 2-d array directly. For example:

    Dim vArr as Variant
    vArr = Range("A1,C1,E1,G1").Value
    will not work, but
    vArr = Range("A1:G1")
    will work

Some videos you may like

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
  •