+ Reply to Thread
Results 1 to 13 of 13

ReDim array inside nested loops

  1. #1
    Registered User
    Join Date
    09-23-2014
    Location
    Washington, DC
    MS-Off Ver
    2011
    Posts
    92

    ReDim array inside nested loops

    Hi All,

    On sheet1 I have a data table with a fixed number of columns and a variable number of rows. I'd like to write a script that runs through column A and adds all the values from that row to an array. I keep getting an error on my ReDim step. Hoping one of you has a solution!

    Thanks in advance!

    Alex

    Please Login or Register  to view this content.

  2. #2
    Registered User
    Join Date
    09-23-2014
    Location
    Washington, DC
    MS-Off Ver
    2011
    Posts
    92

    Re: ReDim array inside nested loops

    Just noticed I left out a piece of what I'm trying to do! I want to be able to look through column A and add values from that row to an array IF the value in column A meets certain criteria. Sorry for the omission!

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: ReDim array inside nested loops

    I see two Redim statements -- which one is causing the error? I would guess it is the second one that includes the Preserve keyword. One important limitation of Redim Preserve is that, when the Preserve keyword is included, you can only change the size of the last dimension (see helpfile here https://msdn.microsoft.com/en-us/lib.../gg251578.aspx ).

    If you have done your job correctly setting the needed size of the array outside of the loop, is it necessary to redim inside of the loop?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: ReDim array inside nested loops

    Reverse the dimensions and transpose at the end.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  5. #5
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: ReDim array inside nested loops

    I agree it is better to Reverse the dimensions and transpose at the end.
    It takes a bit of getting used to having things 90 Degrees to what you want and then performing things on them.
    I wrote some functions to Re Dim Preserve the first dimension. Sometimes i prefer to use them , just so that I can keep things clearer in my own mind when writing code: And I also use there a transpose using simple Array manipulation, as the .Transpose can be either inefficient or a bit quirky

    http://www.excelforum.com/tips-and-t...e-byvalue.html

    Alan
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  6. #6
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    ReDim array inside nested loops. Nories Code with my Functions :)

    So this would be a code example using the simplest of my Functions
    http://www.excelforum.com/tips-and-t...ml#post4378086

    But note, I apply my Functions to a version of Norie’s code. I do this because your code does not work anyway ( Or rather i do not think it does what you want. Nories code does do, I think, what you want.

    Code:
    Please Login or Register  to view this content.
    Alan

  7. #7
    Registered User
    Join Date
    09-23-2014
    Location
    Washington, DC
    MS-Off Ver
    2011
    Posts
    92

    Re: ReDim array inside nested loops

    Thank you for all the help everyone! Conceptualizing the transpose is a little weird, but I think I get it now. I amended Norie's code slightly and keep getting a "subscript out of range" error.

    Please Login or Register  to view this content.

  8. #8
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: ReDim array inside nested loops

    Hi,

    If the array only has one row, transposing will produce a 1 dimensional array, not 2D.
    Last edited by xlnitwit; 10-13-2016 at 10:06 AM.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  9. #9
    Registered User
    Join Date
    09-23-2014
    Location
    Washington, DC
    MS-Off Ver
    2011
    Posts
    92

    Re: ReDim array inside nested loops. Nories Code with my Functions :)

    I narrowed the problem down a bit. It returns an error when the array has only 1 row. Error is on the line where I try to print results

  10. #10
    Registered User
    Join Date
    09-23-2014
    Location
    Washington, DC
    MS-Off Ver
    2011
    Posts
    92

    Re: ReDim array inside nested loops

    Ahh good call, xlnitwit. Here's my final code. Seems to be working well for anyone else who has this problem. Thanks to everyone who helped me out!

    Please Login or Register  to view this content.

  11. #11
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: ReDim array inside nested loops

    Thanks for the Feedback

    BTW. My code from Post # 6 does not use the .Transpose. So it does not have the problem when you only have one row.
    It will also work for only one Row

    The problem arises in Norie's code because when you transpose a 2 Dimensional , 1 "column" Array using the .Transpose, VBA returns a 1 Dimensional Array

    Alan

    P.s.
    At the link I gave_..
    http://www.excelforum.com/tips-and-t...e-byvalue.html
    _.. are also functions which do excactly what .Transpose does in the case of 1 "column" 2 dimensiopnal Array

  12. #12
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: ReDim array inside nested loops

    Hi Don,
    Quote Originally Posted by xlnitwit View Post
    Hi,
    _....the array only has one row, transposing will produce a 1 dimensional array, not 2D.
    If an Array has only one "row" , ( I think ) , .Transposing will produce a 2 D, 1 "column" Array.

    If an Array has only one "column" , ( I think ) , .Transposing will produce a 1 D Array.

    ( At least that is the results that I usually get )

    Norie's code does the building of the Array transposed to allow Re Dim Preserve to be used. So in the case of the OP having one row, the Array that Norie's code builds has 1 "column". This then , on the .Transose , returns a 1 D Array

    Alan

  13. #13
    Registered User
    Join Date
    09-23-2014
    Location
    Washington, DC
    MS-Off Ver
    2011
    Posts
    92

    Re: ReDim array inside nested loops

    Sorry to keep coming back, but as I incorporate Norie's code into my larger code, I keep running into problems. Now I'm getting an error on the transpose step after I put it inside another loop. Anyone have a way around this?

    Please Login or Register  to view this content.

+ 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. [SOLVED] Array VBA Redim help.
    By maistral in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 12-21-2015, 11:19 PM
  2. [SOLVED] Redim on two dimentional array
    By msherifam01 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-05-2013, 02:26 AM
  3. Using the ReDim array function in a multidimensional array in excel
    By Doruli in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-05-2012, 01:43 PM
  4. Excel 2007 : nested array loops
    By cranswick in forum Excel General
    Replies: 1
    Last Post: 06-11-2011, 02:14 AM
  5. Assign sheet value to array... and redim the array size
    By Orange.CL in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-27-2010, 07:18 AM
  6. [SOLVED] ReDim Array
    By Viktor Ygdorff in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-10-2006, 11:09 AM
  7. ReDim Object array as parameter of Variant array
    By Peter T in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-10-2005, 10:06 AM

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