+ Reply to Thread
Results 1 to 51 of 51

VBA issue with Foreach Next

  1. #1
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    VBA issue with Foreach Next

    Hello everyone.

    I am wondering if someone could help me
    I am having some issues with this loop:

    Kind regards,
    Leo.
    Last edited by LeoExcelRookie; 06-10-2021 at 06:36 PM.

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    So...You are wanting to jump to the cell directly after the cell with value "SomeValue" ?
    No need for loop...

    Please Login or Register  to view this content.
    Last edited by sintek; 06-10-2021 at 01:10 PM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Hi,
    Thanks for your reply.
    I just want to simulate a "Next Cell" process for the CELL variable.
    The "some value" is just some random action to happen.
    I am struggling on how to make the Cell variable move/jump to the next value inside itself, since it is a Range from I2:I10

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    I am struggling on how to make the Cell variable move/jump to the next value inside itself
    Not sure what you mean...Perhaps you should upload a sample file and explain in detail what it is you are trying to achieve...

  5. #5
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Hi. Again, thanks for ur reply.
    Here is some pseudo code:

    Thanks for your reply.
    Btw, CT is lovely!
    :-)
    Last edited by LeoExcelRookie; 06-10-2021 at 06:37 PM.

  6. #6
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    Here is some pseudo code:
    as per post 1...this code means nothing unless we can understand what it is you are wanting to achieve...
    See Post 4

  7. #7
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Again...
    Thanks so much...
    Code removed
    Last edited by LeoExcelRookie; 06-10-2021 at 06:21 PM.

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    I'm outta here...Sorry...Good luck...Still have not complied with post 4

  9. #9
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Ok...
    Sorry...
    I will upload it...
    I thought the code was enough...

  10. #10
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    You keep on posting code???
    Explain in detail what it is you are trying to do...and explain what does this mean...
    access the next value inside the Cell variable.

  11. #11
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Sorry for the huge delay and many mistakes with posting code here...
    Here is the file.
    Regards.
    Attached Files Attached Files

  12. #12
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    Okay, so now we have a sample file...Still have not explained in detail what it is you are wanting to achieve???
    See Post 4 and Post 10...
    and explain what does this mean...
    access the next value inside the Cell variable.
    And Please, do not edit a post after a response has been posted...Now, nothing in this thread makes sense...

  13. #13
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    I have a variable called CELL that has values based on a range from A1 to A10.
    These values are not Integers, rather, they are strings.
    What I am trying to do is to loop through them, which i can do, and when
    the loop reaches the fifth element, it "jumps" or "skip" to the
    seventh element, ignoring the sixth one. (line 5)

    I see that the ForEach loop has a "Next Cell" (line 17) that "moves" to the
    next CELL.

    What command is similar or can emulate that same "next cell" behavior
    so i can "jump" or "skip" to the next Cell?


    Regards
    Leo

  14. #14
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    Wow...thank you...Now that makes sense...
    Play around with this...

    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Thank you and sorry for the previous messy posts.
    I have tried that one. But the issue still kind of the same:
    How to access the 7th element of the Cell Variable?

    Please Login or Register  to view this content.

  16. #16
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: VBA issue with Foreach Next

    Why would you need to do that? If you don't want to process that cell, don't include it in the range to start with.
    Rory

  17. #17
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    What you mean 7th element...Do you want the code to exit the loop and go to A7???
    See attached...
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Thanks... I also thought about that. But lets assume I am not able to use that option.
    How would I "skip" to the seventh element in the variable Cell ?

    Regards

  19. #19
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    How would I "skip" to the seventh element in the variable Cell ?
    By adapting the code in Post 2...No loop needed...

  20. #20
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Thanks... thats an interesting solution...
    But I still have to use the Cell variable since it is inside of the ForEach loop and i cant get rid of the ForEach loop

  21. #21
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    But I still have to use the Cell variable since it is inside of the ForEach loop
    We have been going around in circles for days ... You still have not been forthcoming with your requirement...
    Use it for what...We are not mind readers????

  22. #22
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: VBA issue with Foreach Next

    You don't. You add a test to see if you're processing the 6th item and if so, don't actually do anything. It's generally bad practice to mess with loop variables even if you can (which you can't with a For Each).

  23. #23
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Quote Originally Posted by rorya View Post
    You don't. You add a test to see if you're processing the 6th item and if so, don't actually do anything. It's generally bad practice to mess with loop variables even if you can (which you can't with a For Each).
    OK... I didnt know i couldnt access the Cell variable "count" to know which element is being used.
    Looks like i will have to re-do everything from the very beginning.

  24. #24
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Quote Originally Posted by sintek View Post
    We have been going around in circles for days ... You still have not been forthcoming with your requirement...
    Use it for what...We are not mind readers????
    Thanks once again for your help. I think i was trying the "not possible", which is to use the Cell variable of the
    ForEach loop. It was basically that. I will have to start all over again.

  25. #25
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    Why don't you explain what you are wanting to do and we could offer a solution...
    Cell variable of the ForEach loop
    What does this mean...what do you want to do...
    I keep on asking questions but you never respond to them...is it that difficult...You could have had a solution in post 4 already...

  26. #26
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: VBA issue with Foreach Next

    You can add your own counter but you might as well just skip the For Each at that point and use a simple for loop:

    Please Login or Register  to view this content.
    but I agree with Sintek: you haven't really explained what you want to do with the 7th cell, or why this would ever be necessary.

  27. #27
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Quote Originally Posted by rorya View Post
    You can add your own counter but you might as well just skip the For Each at that point and use a simple for loop:

    Please Login or Register  to view this content.
    but I agree with Sintek: you haven't really explained what you want to do with the 7th cell, or why this would ever be necessary.



    I am very sorry if i havent been clear with my posts. My apologies.
    I will try my best now. :-)

    I am assuming that the variable Cell (For Each Cell In Worksheets("mine").Range("A1:A20"))
    is a variable type Array or similar, and, inside it, values, listed from A1 to A20,
    are stored -they are just a list of names.
    So, since my premisse is that Cell is an array, or similar, when its index reaches
    number 5, considering it started from 1, i want to "move" it to index 7, skiping the 6th element
    and let the ForEach loop finish its job -whatever it is-.

    Again, thank you both for your help.

  28. #28
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    so all you want to do is loop the cells in Range("A1:A20")
    If it gets to the 6th cell, it must skip it and then move on to 7

  29. #29
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: VBA issue with Foreach Next

    No, cell is literally one cell (a Range object)

  30. #30
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Yup, thats the idea...
    :-)

  31. #31
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Oh...
    So that was my mistake since the very beggining...
    A false premisse led me to an error... as usual.
    :-)

  32. #32
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Quote Originally Posted by sintek View Post
    so all you want to do is loop the cells in Range("A1:A20")
    If it gets to the 6th cell, it must skip it and then move on to 7
    Yup... Thats the idea...
    -I forgot to quote-

  33. #33
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Quote Originally Posted by rorya View Post
    No, cell is literally one cell (a Range object)
    Oh...
    So that was my mistake since the very beggining...
    A false premisse led me to an error... as usual.
    :-)

  34. #34
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    Yup... Thats the idea...
    That is what post 14 does...

  35. #35
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: VBA issue with Foreach Next

    @sintek & @rorya, gentlemen I must congratulate you on your perfect display of composure.
    torachan.

  36. #36
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    @ Torachan...

    Red wine...lots and lots of red wine....
    images.jpg

  37. #37
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Thank you so much!
    Very kind of you all!
    PVT me a msg so i can send you a Post Card from Brazil.
    Kind regards!
    Leo.

  38. #38
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    But, then again... The idea is to use only the ForEach with the Cell variable and not a For loop.
    :-)

  39. #39
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    So...is your issue solved?

  40. #40
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Quote Originally Posted by sintek View Post
    So...is your issue solved?
    Actually, no.
    Since i HAVE to use the ForEach loop and the Cell variable. Cant use a For loop.
    Thats why i was insisting with the Cell variable as an array -which was my mistake since the start-

    I am about to give up and open a beer... since someone here is drinking wine.

  41. #41
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    So what happened to
    Yup... Thats the idea...
    That is what post 14 does...
    Don't you get it...Explain in layman's terms what you want to do...

    Such as ... I want to run a code snippet that loops through a specific range...During that loop, I want to find criteria "X" when it finds that criteria, it must do the following blah blah blah and then carry on with the loop......................................................

    Otherwise, there is just NO HOPE FOR A SOLUTION...I mean come on...40 post and still no clue what you want to do!!!
    Last edited by sintek; 06-11-2021 at 02:09 PM.

  42. #42
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Ok.
    Let me use the Layman approach.

    I would like to loop through a list of names, located between A1 and A10, using the FOREACH loop, and only that loop.
    When i reach the FIFTH cell or the value of A5 from the CELL variable, i would like to jump to A7 inside that same CELL variable.

    Please Login or Register  to view this content.

  43. #43
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    Like I said...Post 14 does that...!!!

    Step through this code and you will see what I mean...
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 06-11-2021 at 02:59 PM.

  44. #44
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Quote Originally Posted by sintek View Post
    Like I said...Post 14 does that...!!!

    Step through this code and you will see what I mean...
    Please Login or Register  to view this content.
    Ok... Thanks A LOT for your time and attention.
    Was very kind of you and everyone that took 5 min of their time to answer this posts.

    All the best!

  45. #45
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    So...Solved...Yes

  46. #46
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: VBA issue with Foreach Next

    Quote Originally Posted by LeoExcelRookie View Post
    using the FOREACH loop, and only that loop.
    Why do you have to use a For Each loop? You can alter the cell variable in your loop but it will simply reset at the Next cell line.

  47. #47
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Quote Originally Posted by rorya View Post
    Why do you have to use a For Each loop? You can alter the cell variable in your loop but it will simply reset at the Next cell line.
    Hi...
    Again, thanks a lot for your help.
    The issue was finally solved.
    :-)

  48. #48
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    The issue was finally solved.
    Oh please, oh please do share...

  49. #49
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Quote Originally Posted by sintek View Post
    Oh please, oh please do share...
    LOL
    Well... I removed 60% of the code.
    Did a lot of refactoring and "combined" the "Foreach" with an "outside" variable so i could access the values in a way that the Foreach wasnt allowing me.
    :-)
    Again, thanks a lot...
    In few words, i used your example in my "new code".

  50. #50
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA issue with Foreach Next

    So...Not prepared to share your full code so others too...can learn...

  51. #51
    Registered User
    Join Date
    05-23-2021
    Location
    Brasilia
    MS-Off Ver
    2016
    Posts
    29

    Re: VBA issue with Foreach Next

    Quote Originally Posted by sintek View Post
    So...Not prepared to share your full code so others too...can learn...
    I can... not a problem... Its that it is quite messy, and i was embarrassed... but here it goes... :-)

    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] Is there a way to choose a different starting position in a ForEach loop
    By mk01111000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-15-2020, 05:26 AM
  2. [SOLVED] ForEach loop problem
    By peterschein in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-27-2019, 01:24 PM
  3. [SOLVED] Convert data (a bit like a FOREACH?)
    By simonp820 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-04-2016, 03:32 PM
  4. Excel 2007 Sumproduct foreach day in column?
    By jrider64 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-29-2015, 02:27 PM
  5. Foreach Name in a List Paste a Template of Cells
    By Codeman553 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-03-2013, 10:25 AM
  6. [SOLVED] Foreach loop lookup between two worksheets
    By asparak in forum Excel General
    Replies: 7
    Last Post: 07-14-2012, 05:03 AM
  7. [SOLVED] ForEach statement problem
    By excelnut1954 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-31-2006, 05:40 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