Results 1 to 7 of 7

Thread: Better Understanding of 2D Array Writing [VBA]
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    460
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Better Understanding of 2D Array Writing [VBA]

    Code:
    Sub aanewWPcode()
        Dim ary As Variant
        Dim i As Long
        
    
    With ActiveSheet
        ary = .Range("A1").CurrentRegion.Value2
    
    
    End With
    End Sub
    So i have this macro that will assign an entire sheet as an array
    I'm trying to copy the entire 2nd column to the 1st column so

    Blank Part Number
    123
    456
    789

    becomes
    Blank Part Number
    123 123
    456 456
    789 789

    i don't want to use i = 2 to ubound
    i want to write this using the range.value = ary method, but i'm not sure how to write that line.

  2. #2
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    460
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Better Understanding of 2D Array Writing [VBA]

    ah.....i obviously didnt spend enough time thinking about this
    Code:
    Sub aanewWPcode()
        Dim ary As Variant
        Dim i As Long
        Dim lastRow As Long
        
    
    With ActiveSheet
    ary = .Range("A1").CurrentRegion.Value2
    
    For i = 2 To UBound(ary)
    ary(i, 1) = ary(i, 2)
    Next i
    
    .Range("A1").Resize(UBound(ary), 1).Value = ary
    
    End With
    End Sub
    do you think theres a better way to write this though?
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Better Understanding of 2D Array Writing [VBA]

    What you have done is probably the safest way but you could also use
    Code:
    .Range("A1").Resize(UBound(ary), 1).Value = Application.Index(ary, 0, 2)
    but you may have problems with large amounts of data.
    I can't remember the limit off hand, but I think it's about 32000 rows.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    460
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Better Understanding of 2D Array Writing [VBA]

    Quote Originally Posted by Fluff View Post
    but I think it's about 32000 rows.
    yeah that won't work with what i have.
    the plan now is to group all of my data (ranging from 3000-100,000+ rows of data) together in an array to make changes to all of it at once rather than segmenting the data and calling on non array macros.
    any reason why that would cause any issues?

    its seeming like i can make this entire process which takes well over 30 min for my smallest dataset to literal minutes.
    I.E right now i have it to where two arrays are "talking to each other" and making changes to the data this way

    Code:
    Sub aanewWPcode()
        Dim ary As Variant
        Dim i As Long
        Dim lastRow As Long
        
    
    With ActiveSheet
    
    ' assign two arrays; one for reference and one for printing values
    ary = .Range("A1").CurrentRegion.Value2
    ary2 = .Range("A1").CurrentRegion.Value2
    
    ' transfer correct data to proper columns using the two arrays
    For i = 2 To UBound(ary)
    ary2(i, 1) = ary(i, 2)
    ary2(i, 2) = ary(i, 38)
    ary2(i, 3) = ary(i, 41)
    ary2(i, 4) = ary(i, 69)
    ary2(i, 5) = ary(i, 8)
    ary2(i, 6) = ary(i, 9)
    ary2(i, 7) = ary(i, 4)
    ary2(i, 8) = ary(i, 13)
    ary2(i, 9) = ary(i, 14)
    ary2(i, 10) = ary(i, 11)
    ary2(i, 11) = ary(i, 12)
    
    ' if statement for determing map or retail pricing
    If ary(i, 7) > 0 Then
    ary2(i, 12) = ary(i, 7)
    ElseIf ary(i, 6) > 0 Then
    ary2(i, 12) = ary(i, 6)
    Else
    ary2(i, 12) = "err"
    End If
    
    ary2(i, 13) = ary(i, 19)
    ary2(i, 14) = ary(i, 36)
    ary2(i, 15) = "img"
    ary2(i, 16) = "title"
    ary2(i, 17) = "desc"
    ary2(i, 18) = "qty"
    ary2(i, 19) = ary(i, 15)
    Next i
    
    ' print the array to the sheet
    .Range("A1").Resize(UBound(ary2), 19).Value = ary2
    End With
    
    
    
    End Sub
    would this be problematic in any way if there were 100,000 rows and 19 columns of data?
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Better Understanding of 2D Array Writing [VBA]

    The only problem that I can think of offhand is it might be more data than your computer can handle so you may get an something along the lines of "Out of Memory" or "Out of Stack space"
    The only way to know, is to try it.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    460
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Better Understanding of 2D Array Writing [VBA]

    Quote Originally Posted by Fluff View Post
    The only way to know, is to try it.
    Excellent
    if that happens i'll cry to my IT department for more RAM
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Better Understanding of 2D Array Writing [VBA]

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

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
  •