We help IT Professionals succeed at work.

Resolve empty cell results in Create Combinations Script

A bug in the attached script creates all blank output when the cell references have not data.

For example, cell BR10 is blank, so the "Numbers_3" sheet has not data. It has to do with blank cells, because when I put data in that cells I get cell data in "Numbers_3" sheet.

I would like to resolve it where I can have blank cells because if I have to put data beyond number 70 I will need another script that will delete any row containing any number above 70.


blank cells create blank resultsGreen represents blank cells

The attached file contains data in all the "Numbers_X" sheets but after the macro is run only Numbers_2 has data and it is the only filled line.
CreateCombinationsBug_sheetsblank-c.xlsb
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Here's a solution to the problem, but I believe you are making a mistake when you don't attach your most recent workbook. The reason is that the workbook you attached does not contain code that allows the deletion of sets that are not valid as does the workbook prior to this one. I know that the problem you are trying to fix has nothing to do with that, but if you did try to run the Q_28233532 sub in that workbook it would fail because it won't have the fix I just made. To make a long story short, you should just have one workbook.
29173484.xlsb

Author

Commented:

 if you did try to run the Q_28233532 sub in that workbook it would fail

I agree.


To rectify, I need know how to rename each script? This way I can remember which one to run based on what I step I need.

I've used the properties dialog box to rename but when I view macros the old name is shown.



Here is where I need the name to change.


This is the most recent of the remove invalid decis file I have.

RemovesBothDecisandAndRunsFaster_2-21-2020.xlsb


You can show me how to put both into one workbook here or do it for me, if it's complicated.

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try this version. In it you'll find two buttons on Sheet1 that will run code in the workbook. Hopefully the button captions are self-explanitary but I can change them if you want me to. If you ever want to know which macro is run by a button then right-click on the button, choose 'Assign Macro...' and a window like this will pop up. I've highlighted the name of the macro in yellow. If you have more than one workbook open you should change the 'Macros In:' drop-down to say 'This Workbook'.
2020-02-22_06-06-14.png
A question just occurred to me and that is that after you click the 'Create Sets' button, will you always want to click the 'Remove Invalid' button? If so I can remove the latter and have 'Create Sets' do both.
29173484a.xlsb

Author

Commented:

Martin,


I'd rather keep them separate to aid in troubleshooting. 


When I run the "create sets" button I only get up to "3-9" combinations. I have changed "ZA" to "BR" and I don't see any other variables that need updating.


The code in the file I attached in the original post (above) makes all combinations up to the last number possible.

 Please advise.

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In order for me to not get an 'out of memory' error when I click 'Create Sets' I have to limit the number of columns on Sheet1 that the code looks at. When I make the range B:AH it runs fine and it produces Numbers_3, 4, 7, 8, 10, 11, 17, 18, 3 sheets as it should so I don't understand what you mean when you say "I only get up to "3-9" combinations".

Author

Commented:


In order for me to not get an 'out of memory' error when I click 'Create Sets' I have to limit the number of columns 


I understand that. Which is why I changed the variables to max numbers siBCE my PC can do so without running out of memory. BTW. If all you need is more memory, I may have some to give you. Send me the specs, via private message or email.



 I don't understand what you mean when you say "I only get up to "3-9" combinations".


The picture I posted shows the last few combinations in the numbers_3 group, after running the script. The script should continue until the 60’s and end with 70 in the last cell to the right. This means the code is not making all of the combinations that are possible.

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
When create sets in 29173484a.xlsb changing the ranges to look like this

 
  For Each vntRange In Array([B10:AX10], _
                             [B11:AX11], _
                             [B12:AX12], _
                             [B13:AX13], _
                             [B14:AX14], _
                             [B15:AX15], _
                             [B16:AX16], _
                             [B17:AX17], _
                             [B18:AX18], _
                             [B19:AX19])

Open in new window

it produces Numbers_3, 4, 7, 8, 10, 11, 17, 18, 3 sheets and I get this so I don't know what's going on.
2020-02-22_09-30-04.png

Author

Commented:

I've reviewed the code for the code that creates combinations for both and there are line differences between them.


You might be able to duplicate the error I am getting by clearing cells AX10, AX11, AX12, AX13, etc. so you have at least one blank cell in the array. Then you may be able to duplicate what I am getting after running   29173484a.xlsb

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I did as you suggested no saw no difference. Here's a workbook with my ranges where I went even further.
29173484b.xlsb

Author

Commented:

Martin,


Ran all the codes. After adding both codes in the same workbook the results change. Here's what I get after running 29173484b.xlsb


This incorrect output occurs after adding the "RemoveInvalidDecis" to the "CreateCombinations" script. I know this because I have made several attempts and I get the same results. However, is I run the "RemoveInvalidDecis" script separately (meaning in a different file) it seems to work as intended.



Author

Commented:

P.S.  I see three modules instead of the two I expected. Please remove any modules not needed to avoid confusion.

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Your terminology is incorrect; there is just one module, Module1. Module1 contains one Sub,  CreateCombinations and two macros, DriveCombinationCreation and RemoveInvalidDecis. The difference between a Sub and a macro is a macro may be called from a worksheet and a Sub may not. Everything in the code is needed. When the 'Create Sets' button is clicked it executes DriveCombinationCreation, which in turn calls CreateCombinations for each range on Sheet1. When the 'Remove Invalid' button is clicked it executes the RemoveInvalidDecis macro,

You seem to be saying that you are modifying the code, and you should not generally do that. You also should not be executing the code directly. Instead you should limit yourself to just clicking one of the buttons.

With all that in mind, using the 29173484b.xlsb workbook I uploaded (unmodified). change the ranges in DriveCombinationCreation to encompass B:BR and click 'Create Sets'. Do you get the expected results.

Author

Commented:

The 29173484b.xlsb workbook you uploaded (unmodified), yields same results as above. Even if I do not change AX to BR.


BTW. I also looked at Sheets 4-18 and each yields numbers  beyond the first number in the combination. See below for sample.



Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
The 29173484b.xlsb workbook you uploaded (unmodified), yields same results as above.
If this post of yours is what you are referring to when you say "above", then I don't understand what you mean.

In any case there was a bug in the original code that caused the 5s to appear in Numbers_4 which I've corrected in the attached workbook. I also modified the DriveCombinationCreation macro so that it looks like this:

    #If Win64 Then
        strLastCol = "BB"
    #Else
        strLastCol = "AZ"
    #End If
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  For Each objWorksheet In Thisworkbook.Worksheets
  
      If Left$(objWorksheet.Name, 8) = "Numbers_" And _
         IsNumeric(Mid$(objWorksheet.Name, 9)) Then
         objWorksheet.Delete
      End If

  Next objWorksheet
  
  Application.DisplayAlerts = True
  
  lngNumbers = 0&

    For Each vntRange In Array(Range("B10:" & strLastCol & "10"), _
                             Range("B11:" & strLastCol & "11"), _
                             Range("B12:" & strLastCol & "12"), _
                             Range("B13:" & strLastCol & "13"), _
                             Range("B14:" & strLastCol & "14"), _
                             Range("B15:" & strLastCol & "15"), _
                             Range("B16:" & strLastCol & "16"), _
                             Range("B17:" & strLastCol & "17"), _
                             Range("B18:" & strLastCol & "18"), _
                             Range("B19:" & strLastCol & "19"))

Open in new window

Lines 1 to 5 tell the compiler to use one of the two values depending on whether or not you are using 64 bit, and lines 23 to 32 use that value in defining the ranges. That way you and I can test without having to manually change the ranges. In the future if you decide to expand or contract the range you only need to change line 2.
29173484c.xlsb
Fabrice LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:

Can you explain us what this algorythm is supposed to do ?
There might be better ways to do it.


I understood up to the "Get the number of combinations" part. Beyond that, it is nebulous (Something with cartesian products ?).


Side notes (so much to say in such little piece of code …):
- Use meaningfull variables and function names.
- Give up hungarian notation, it provides nothing usefull.
- Fully qualify your variables, to avoid potential conflicts.
- Be explicit rather than implicit.
- Do not rely on "Active" objects, they are unpredictable.
- Disable warnings and errors for as little time as possible (best is one instruction).
- Your code break the SRP (Single Responsibility Principle), and should be broken into several smaller functions that do one thing, and do it well.
- Get rid of those performances ehencer instructions, only add them when your code produce the expected result.
- Do not store the err object members for the sole purpose of displaying it.
- Working with collection might be easyer than arrays, since collections auto expand.