+ Reply to Thread
Results 1 to 7 of 7

Need help on Basic Two Dimensional Dynamic Array

  1. #1
    Registered User
    Join Date
    02-13-2014
    Location
    Boston, United States
    MS-Off Ver
    Excel 2010
    Posts
    12

    Need help on Basic Two Dimensional Dynamic Array

    Hello,

    I am new to VBA. I have managed to write the following code:

    Please Login or Register  to view this content.
    The purpose of this code is to loop through each row in a table (C8:W37), find the first non-zero value, and copy paste the value in row 6 of that column into the same row but in column 2. The above code works just fine for what I want it to do.

    The problem is that the number of the rows in the table may change. So instead of Array(8 To 37, 3 To 23) I would want it to be like Array(8 To x, 3 To 23), where x would equal = ActiveSheet.Range("C8").End(xlDown).Row

    However, I cannot seem to accomplish this. Also for another purpose I would like to adapt this code to reverse the loop so that it work backwards like lets say Array(8 To 37, 23 To 3), but I cannot seem to accomplish this either. I am stuck and I don't know how to proceed to resolve these issues.

    Any assistance would be highly appreciated.
    Last edited by chrixton; 03-02-2014 at 12:48 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    12-25-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    506

    Re: Need help on Basic Two Dimensional Dynamic Array

    Quote Originally Posted by chrixton View Post
    The problem is that the number of the rows in the table may change. So instead of Array(8 To 37, 3 To 23) I would want it to be like Array(8 To x, 3 To 23), where x would equal = ActiveSheet.Range("C8").End(xlDown).Row
    Try replace "Dim Array(8 To 37, 3 To 23) As Long" with this two lines.
    Please Login or Register  to view this content.
    Quote Originally Posted by chrixton View Post
    Also for another purpose I would like to adapt this code to reverse the loop so that it work backwards like lets say Array(8 To 37, 23 To 3), but I cannot seem to accomplish this either.
    Try change "For j = 3 To 23" to this.
    Please Login or Register  to view this content.
    note: I've change the variable name to "lngArray" instead of "Array" by trying to avoid using reserved word in VBA


    _______________________________________________________________________________________________________________________________
    1. Click on the * Add Reputation if you think someone helped you
    2. Mark your thread as SOLVED when question is resolved

    Alvin
    Last edited by alvin-chung; 03-02-2014 at 01:15 PM.

  3. #3
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,759

    Re: Need help on Basic Two Dimensional Dynamic Array

    This is how to do what you want. You do not need to use an array, or store values somewhere to be able to process them. This code will start at the bottom of column C, and find the first non-zero value in each row - the number of columns can vary as well.

    Please Login or Register  to view this content.
    If it is possible that a row will not have a non-zero value in it, then you should make sure to include a way out:
    Please Login or Register  to view this content.
    Last edited by Bernie Deitrick; 03-02-2014 at 01:18 PM.
    Bernie Deitrick
    Excel MVP 2000-2010

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,233

    Re: Need help on Basic Two Dimensional Dynamic Array

    Try:

    Please Login or Register  to view this content.


    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Registered User
    Join Date
    02-13-2014
    Location
    Boston, United States
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Need help on Basic Two Dimensional Dynamic Array

    Wow, thanks a lot everybody. Every solution offered works.

    Thank you all once again.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,233

    Re: Need help on Basic Two Dimensional Dynamic Array

    You're welcome. Thanks for the rep.

  7. #7
    Valued Forum Contributor
    Join Date
    12-25-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    506

    Re: Need help on Basic Two Dimensional Dynamic Array

    Hi chrixton,

    You're welcome, thanks for the feedback and reputation


    _______________________________________________________________________________________________________________________________
    1. Click on the * Add Reputation if you think someone helped you
    2. Mark your thread as SOLVED when question is resolved

    Alvin

+ 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. Replies: 6
    Last Post: 09-25-2013, 10:08 PM
  2. Populate a Single Dimensional Array with two Dim Array (matrix)
    By Benjamin1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-03-2011, 12:49 PM
  3. Dynamic 2 dimensional array in VBA class
    By dlx in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-21-2010, 09:20 PM
  4. Creating a 2-dimensional array from a 1-dimensional list
    By guywithcamera in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-27-2008, 06:34 PM
  5. Create One-Dimensional Array from Two-Dimensional Array
    By Stratuser in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-23-2005, 05:06 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