+ Reply to Thread
Results 1 to 16 of 16

For Each Loop on the 2nd Dimension Array Only

  1. #1
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    287

    For Each Loop on the 2nd Dimension Array Only

    How would one go about using a For Each Loop to the second dimension only? It'd be easy to do it with UBound and LBound with a For Loop but I'm struggling in getting this to work with a For Each Loop.

    [CODE]
    i = 1
    j = 1
    For Each j in Arr (i, j)
    Debug.Print Arr(i, j)
    Next j
    [CODE]

    Obviously it's not recognising the upper limit in the 2nd dimension. Surely it's possible to use a For Each Loop on the 2nd dimension in an array?

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: For Each Loop on the 2nd Dimension Array Only

    values in cells.jpg


    Please Login or Register  to view this content.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    287

    Re: For Each Loop on the 2nd Dimension Array Only

    Thanks Kev, to clarify you're saying that it's not possible with a For Each Loop?

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: For Each Loop on the 2nd Dimension Array Only

    This also works for a horizontal array:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  5. #5
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: For Each Loop on the 2nd Dimension Array Only

    you're saying that it's not possible with a For Each Loop?


    Dimension 1 upper bound
    - UBound(myArray, 1)

    Dimension 2 upper bound
    - UBound(myArray, 2)

    I hope that clarifies it for you

  6. #6
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    287

    Re: For Each Loop on the 2nd Dimension Array Only

    Thanks guys, my query is - is it not possible to loop the 2nd dimension with a For Each Loop?

  7. #7
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    287

    Re: For Each Loop on the 2nd Dimension Array Only

    Quote Originally Posted by kev_ View Post


    Dimension 1 upper bound
    - UBound(myArray, 1)

    Dimension 2 upper bound
    - UBound(myArray, 2)
    Thanks Kev, as I mentioned in my opening post I was well aware of attaching the UBound and LBound functions. I want to know if it's possible using a For Each Loop as stipulated in this tutorial; but it works on the whole array.

    My query is can a For Each Loop be specified on the second dimension only?

  8. #8
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: For Each Loop on the 2nd Dimension Array Only

    You are misunderstanding what you are seeing

    EDIT
    Apologies - our posts are crossing
    - I now understand that you ONLY want to consider FOR EACH...
    Last edited by kev_; 03-12-2018 at 01:00 PM.

  9. #9
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: For Each Loop on the 2nd Dimension Array Only

    Use my example of 10 values - which values do you want?

  10. #10
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: For Each Loop on the 2nd Dimension Array Only

    To use For each loop and the 2nd element, then you want every other element in the array - like this:
    Please Login or Register  to view this content.
    or with a message box
    Please Login or Register  to view this content.
    ForEach.jpg
    Last edited by kev_; 03-12-2018 at 01:01 PM.

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,504

    Cool

    Hi !

    Quote Originally Posted by Dal123 View Post
    My query is can a For Each Loop be specified on the second dimension only?
    At beginner level just using INDEX worksheet function :

    PHP Code: 
        For Each V In Application.Index(Arr, , 2)
            
    Debug.Print V
        Next 
    Do you like it ? So thanks to click on bottom left star icon Add Reputation !

  12. #12
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: For Each Loop on the 2nd Dimension Array Only

    With my example array this returns 10,20,30,40,50 which is all the values of the 2nd element
    Please Login or Register  to view this content.

  13. #13
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,504

    Re: For Each Loop on the 2nd Dimension Array Only


    Just try my original code ‼ Without Transpose …

  14. #14
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: For Each Loop on the 2nd Dimension Array Only

    Yes that returns 2 and 20

    Ok you can return the values whichever way is up,
    ... but I am obviously missing the point

    We have returned the values with both For i = .. and For Each...
    ... what is now missing?
    Last edited by kev_; 03-12-2018 at 02:09 PM.

  15. #15
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,504

    Re: For Each Loop on the 2nd Dimension Array Only


    Obviously Dal123 must crystal clear explain his need with a complete code (as we can't guess how the array is loaded !)
    like he should do when creating a new thread, from the beginning until expected result …

  16. #16
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    287

    Re: For Each Loop on the 2nd Dimension Array Only

    Apologies if I'm being unclear I was trying to not bloat the thread with a superfluous thread. I'm sorry for wasting all of your time with lacking information I'm not sure if I should start a new clean thread tomorrow as I've made a right mess of this one, sorry guys.

+ 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. add one dimension to array
    By shaykos in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 12-04-2015, 10:47 PM
  2. Excel: Sum each dimension of an Array
    By cmore in forum Excel Programming / VBA / Macros
    Replies: 42
    Last Post: 06-08-2015, 11:35 PM
  3. [SOLVED] Sort a 2 dimension array
    By tradinup2 in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 02-28-2013, 10:29 AM
  4. Does filling part of an array from a range re-dimension the array?
    By barryleajo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-04-2011, 10:09 AM
  5. Array dimension declaration
    By yellephant in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-29-2007, 08:44 AM
  6. Array dimension
    By Henrietta Klaus in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-21-2006, 11:10 AM
  7. Mutli-dimensional Array to Single-Dimension Array
    By Blue Aardvark in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-15-2005, 05:05 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