+ Reply to Thread
Results 1 to 12 of 12

Iterating through a range of cells with a message at the end

  1. #1
    Registered User
    Join Date
    03-15-2021
    Location
    Engalnd
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Question Iterating through a range of cells with a message at the end

    I have data in two columns - column A and column B. Values in column b can be either 0, 5 or 6. I want to iterate through all the cells in column B. If the value of a particular cell =0, then I want to add the value from the cell to the left of it to an array; if it is <>0, then I just want to go on to the next cell. Once I have finished the iteration, I want a Message Box to appear depending on if the array is empty or not. In the array = 0, then the message should be "No missing data"; if the array has data, then "Missing data for: " + the values stored within the array.

    I've never really worked with arrays in VBA so I'm not too sure how to proceed.

    This is how I've structured the code so far:

    Please Login or Register  to view this content.
    Any help with the array bits of the code would be most appreciated. I've put in the bits where I'm unsure how to proceed.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,869

    Re: Iterating through a range of cells with a message at the end

    One of these will work, depending on if you have blanks or zeroes:

    Please Login or Register  to view this content.
    If you have both, then you could use:

    Please Login or Register  to view this content.
    The rest of the issue depends on whether the cells are blank or filled with the number 0 or both....
    Last edited by Bernie Deitrick; 02-05-2024 at 03:13 PM.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    03-15-2021
    Location
    Engalnd
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Re: Iterating through a range of cells with a message at the end

    In the range being iterated there will never be a blank cell. It will always have either 0, 5 or 6.

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,869

    Re: Iterating through a range of cells with a message at the end

    See if this works:

    Please Login or Register  to view this content.
    Last edited by Bernie Deitrick; 02-05-2024 at 06:12 PM.

  5. #5
    Registered User
    Join Date
    03-15-2021
    Location
    Engalnd
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Re: Iterating through a range of cells with a message at the end

    I've amended the For loop to the following. However, when it does encounter cell = 0 it throws an Out of Range Error on the ReDim line, which I'm not quite sure why.

    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,869

    Re: Iterating through a range of cells with a message at the end

    You cannot 'preserve' until after it has been dimensioned, so add this:

    Please Login or Register  to view this content.
    Then it should be fine.

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

    Re: Iterating through a range of cells with a message at the end

    Another Option...

    Please Login or Register  to view this content.
    Or, if you want to loop...
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 02-06-2024 at 11:56 AM.
    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!!!

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2403 (Windows 11 22H2 64-bit)
    Posts
    78,120

    Re: Iterating through a range of cells with a message at the end

    Administrative Note:

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.

    Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

    (Note: this requirement is not optional. No help to be offered until you provide a link or, for members with fewer than 10 posts, a comment telling us where else you have posted this query.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  9. #9
    Registered User
    Join Date
    03-15-2021
    Location
    Engalnd
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Re: Iterating through a range of cells with a message at the end

    Apologies for this. I will take better care with the rules going forward.

    As for the cross-post, the forum is not allowing me to link directly as I still have too few posts, so I'll have to post it this way: vbaexpress. com/forum/showthread.php?71370-Iterating-through-a-range-of-cells-with-a-message-at-the-end

    I have, in the meantime, been able to sort out the query in my own way.

    Quote Originally Posted by AliGW View Post
    Administrative Note:

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.

    Please see about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important:

    (Note: this requirement is not optional. No help to be offered until you provide a link or, for members with fewer than 10 posts, a comment telling us where else you have posted this query.)

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

    Re: Iterating through a range of cells with a message at the end

    I have, in the meantime, been able to sort out the query in my own way
    Well...Tx for letting us know...sadly time wasted offering you solutions which you will not be making use of...

  11. #11
    Registered User
    Join Date
    03-15-2021
    Location
    Engalnd
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Re: Iterating through a range of cells with a message at the end

    Quote Originally Posted by sintek View Post
    Well...Tx for letting us know...sadly time wasted offering you solutions which you will not be making use of...
    Apologies again. Not that it's much consolation for you (and I'm not meaning to add insult to injury), but I did learn quite a bit from studying all the solutions offered here.

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

    Re: Iterating through a range of cells with a message at the end

    but I did learn quite a bit from studying all the solutions offered here
    Thank you...Glad we could contribute...

+ 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] Iterating Across Cells if No Match
    By Flynn380 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-01-2022, 12:59 AM
  2. Grabbing text by iterating through multiple cells?
    By noname91 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-26-2020, 03:31 PM
  3. Iterating through Cells In a column
    By Cnelson0789 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-06-2018, 10:58 PM
  4. Help iterating through range of cells and formatting data for output
    By darxide23 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-04-2016, 06:45 PM
  5. Quick question - iterating through a range and referring to cells
    By Ariadust in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-10-2012, 02:43 PM
  6. Iterating rows, calc cells in dynamic sheet
    By robd in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-20-2006, 01:37 PM
  7. [SOLVED] Iterating through cells, comparing values
    By kingpin2502 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-11-2005, 02:05 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