+ Reply to Thread
Results 1 to 14 of 14

ignore blank values when looping

  1. #1
    Forum Contributor
    Join Date
    03-16-2004
    MS-Off Ver
    2016
    Posts
    175

    ignore blank values when looping

    I have 2 workbooks, Moo 1&2. Moo2 is a summary of Moo1. I have to be careful in Moo1 to enter the percentage for the factor adjacent to the last occurrence of that factor or the macro will give a blank for that factor in Moo2.

    In the examples, A/1,2 is shown as a blank in Moo2 because it is not in the last occurrence of that factor. The other two factors are picked up because they are in the last occurrence of that factor. There is always only one percentage for a set of factors but I would like to not have to reposition the percentage as the factor list grows longer to accommodate the macro. The various factors can be several hundred rows long but are always separated by a space as shown. I would like the macro to be able to pick up the percentage of the factor without having it to be in a specific position. (The macro is in Moo2)
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: ignore blank values when looping

    I have not rewritten this (althought probably using range.find instead of application.match would be better), but just added checking if last value is empty and if so "pressing Ctrl+Up" to find non-empty value above. Then just ensuring if it is still the same factor as currently examined:

    Please Login or Register  to view this content.
    PS. The code is at the moment in worksheet module. It would be "more standard" to move it to a standard module (like Module1).

    PS2. Please edit your profile and add information about excel version you are using
    Last edited by Kaper; 06-06-2020 at 03:10 AM.
    Best Regards,

    Kaper

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: ignore blank values when looping

    OK, returned to it and quickly rewritten to Find, because I noticed that looping c variable shall rather go in Moo2 than Moo1 to save a time. So see also this:

    Please Login or Register  to view this content.

  4. #4
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: ignore blank values when looping

    This is another option that should return the last non-blank value from file Moo1

    Please Login or Register  to view this content.
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

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

    Re: ignore blank values when looping

    Another Option...
    Please Login or Register  to view this content.
    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!!!

  6. #6
    Forum Contributor
    Join Date
    03-16-2004
    MS-Off Ver
    2016
    Posts
    175

    Re: ignore blank values when looping

    Thanks for your responses but I can't use any of them. The second macro by Kaper is limited to 3 sets of factors. I have over a thousand factor sets. The workbooks were just examples. The initial macro by Kaper also did not work when I tried re positioning the percentage figure to see if it would pick it up. It didn't.

    I haven't been successful in adapting sintek's macro into the real spreadsheet's parameters. Also if I don't remember to click the right sheet before I run the macro, it pastes on the wrong sheet. But thanks for trying.

  7. #7
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: ignore blank values when looping

    Have you tried the code in post #4 ? What happened ? Any error message ?

  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: ignore blank values when looping

    (The macro is in Moo2)
    I haven't been successful in adapting sintek's macro into the real spreadsheet's parameters
    All the above code works for your provided parameters in Post 1 and all provide the solution required...
    If this is not the case in your actual files then you need to clarify...

  9. #9
    Forum Contributor
    Join Date
    03-16-2004
    MS-Off Ver
    2016
    Posts
    175

    Re: ignore blank values when looping

    Hi nankw83

    This part of the code limits the factor sets to the 3 in the example. My spreadsheet has over a thousand factor sets.

    If a(x, 4) <> "" Then If Not Dic.exists(a(x, 1)) Then Dic.Add a(x, 1), a(x, 4) Else Dic(a(x, 1)) = a(x, 4)

  10. #10
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: ignore blank values when looping

    Quote Originally Posted by light View Post

    This part of the code limits the factor sets to the 3 in the example. My spreadsheet has over a thousand factor sets.

    If a(x, 4) <> "" Then If Not Dic.exists(a(x, 1)) Then Dic.Add a(x, 1), a(x, 4) Else Dic(a(x, 1)) = a(x, 4)
    Unless I am not understanding what your're saying then no, the above part of the code doesn't limit the factor sets to 3 … If you have already tried it with your actual file with thousands of factor sets, what happens ? Does it error out ? Does it work but gives the wrong info ? You need to elaborate more so we can know what to adjust to fit your needs

  11. #11
    Forum Contributor
    Join Date
    03-16-2004
    MS-Off Ver
    2016
    Posts
    175

    Re: ignore blank values when looping

    Quote Originally Posted by sintek View Post
    All the above code works for your provided parameters in Post 1 and all provide the solution required...
    If this is not the case in your actual files then you need to clarify...
    Hi sintek

    In the real spreadsheet, the factors in MOO1 start in Col "S4" 20,000 rows deep and the Percent starts in Col "T4",both have headers. In the results file, Moo 2, the Factors start in Col A1 and the percent is in Col B1 with no headers. For some reason, I can't make it work.

  12. #12
    Forum Contributor
    Join Date
    03-16-2004
    MS-Off Ver
    2016
    Posts
    175

    Re: ignore blank values when looping

    nankw83

    There is no error message, it just does not pick up data beyond the example. I tried adding a 4th set of factors to the example sheet, MOO1, and it picked up the original 3 factors but not the newly added 4th one.

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

    Re: ignore blank values when looping

    Hi sintek

    In the real spreadsheet, the factors in MOO1 start in Col "S4" 20,000 rows deep and the Percent starts in Col "T4",both have headers. In the results file, Moo 2, the Factors start in Col A1 and the percent is in Col B1 with no headers. For some reason, I can't make it work.
    So why not upload sample files that depict your actual setup if you know that you are not able to change code to implement into your actual workbook?

    This is exactly as you explained it...If this does not work then you need to upload a sample file that depicts your actual setup so that we do not waste valuable time writing unnecessary code...
    Attached Files Attached Files
    Last edited by sintek; 06-08-2020 at 03:41 AM.

  14. #14
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: ignore blank values when looping

    Quote Originally Posted by light View Post
    The second macro by Kaper is limited to 3 sets of factors.
    That's not true. Also similar comment to other macros.
    Probably your sample is too far away from real scheme, and you just cannot adopt it to your needs.

    No representative data = just wasting our time

+ 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. need to ignore blank values in AVERAGEA formula
    By powersml07 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-30-2018, 01:47 PM
  2. sumproduct formula to ignore blank values
    By Look, More, What in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-17-2018, 05:43 PM
  3. Ignore blank cells from most common values
    By Wazo11 in forum Excel General
    Replies: 1
    Last Post: 05-12-2016, 01:10 AM
  4. Ignore Blank in Counting Consecutive Values
    By bahanley in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-11-2016, 03:17 PM
  5. [SOLVED] Get top 10 values but ignore with blank cell if there's less than 10 values
    By werko in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-22-2015, 09:16 PM
  6. [SOLVED] Ignore Blank Values While Using INDEX
    By jokorey in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-08-2014, 01:58 PM
  7. Array Function - ignore blank values
    By gillemi in forum Excel General
    Replies: 6
    Last Post: 06-13-2006, 04:10 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