+ Reply to Thread
Results 1 to 5 of 5

Accelerate nested loops

  1. #1
    Registered User
    Join Date
    11-04-2012
    Location
    Paris
    MS-Off Ver
    Excel 2010
    Posts
    3

    Accelerate nested loops

    Hi everybody, this is my first visit and post on this forum!

    Here is my question:
    I've got an Array of Integers, DataInt(), with n rows and t columns. Columns are filled with values 1, 2 or 3. I need to compare all possible pairs of columns, in counting the 9 possible combinations (11, 12, 13, 21, 22, 23, 31, 32, 33).

    I've written a macro which works fine, but slowly as I'm generally working with t around 10,000 and n around 200.

    Does anybody have any trick to significantly fasten this macro. i, n and t are declared as Long (faster than Integer), along with DataInt().

    Thanks in advance!

    My code :
    Please Login or Register  to view this content.

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Accelerate nested loops

    Assuming data starts in A1, give this a try. I ran it on a test workbook that had 10,000 rows and 200 columns and it completed in about 5 seconds:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    11-04-2012
    Location
    Paris
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Accelerate nested loops

    Hi Tigeravatar, thanks for answering. I'm not working on a WorkSheet but on a declared Array within VBA. Do you think your code would apply?
    Also, it's not only 10,000 columns but 10,000 compared to themselves, as I have to consider all possible pairs of columns.
    Thank you

  4. #4
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Accelerate nested loops

    Unfortunately the Countifs method wouldn't apply to a declared array within VBA. Also, by checking every possible pair of columns that will increase the calculation time exponentially as well. Not sure you can get around that.

  5. #5
    Registered User
    Join Date
    11-04-2012
    Location
    Paris
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Accelerate nested loops

    Thanks anyway for taking time for answering this.

+ 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