Re: Tom Ogilvy's VBA code

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Maxi,

One thing I Noticed is that if you Change the Criteria and Run it again
it Adds the NEW Combinations Output to the Bottom of the OLD ( Previous
) Combinations Output.

All the Best.
Paul

Maxi wrote:
Hi! Paul,

I know its kind of confusing but even I am looking out for some easy
way and that it the reason I have posted this code here to seek help
from some excel vba gurus.

Maxi

Paul Black wrote:
Thanks Maxi,

Is there an easy way to use it please.

All the Best.
Paul

Maxi wrote:
You can get the entire code by downloading the file from the following
link:

http://www40.brinkster.com/Maxlott/try.htm

Paul Black wrote:
Hi Maxi,

This is Interesting, will it be Possible for you to Post the Entire
Code that you are Using Please.

All the Best.
Paul

Maxi wrote:
The problem is solved. However I have few doubts

Summary of the problem
======================

I got hold of a vba code by Myrna Larson (July 25, 2000,
Microsoft.Public.Excel.Misc) which is used to list permutation and
combinations. It requires two 3 things:
1. What do you want to list (Permutations/combinations) Enter "C" or
"P" in cell A1
2. How many numbers do you want in a combination. Enter this in cell A2
3. List numbers vertically from cell A3 of which you need to list
combinations.

I have modified this vba code and added two more criteria:
1. How many matches. Enter in cell B1
2. What frequency? Enter in cell C1

Please download this excel file to see how it runs.
http://www40.brinkster.com/Maxlott/try.htm

In this example, I have 17 draws listed in range F1:Y17. Following is
the criteria I have used
A1 = C (I want to create combinations)
A2 = 3 (I want to create combinations of 3 numbers each)
B1 = 3 (I want to match all 3 numbers)
C1 = 6 (List combinations only if all the 3 numbers (matches) in a
combination apprears in more than or equal to 6 draws.

If you run the macro (DoIt) it will list only 42 combiations out of
19380 (=COMBIN(20,3)*17). These 42 combinations comply to the above
criterias given. If you check any combinations, you will see that all
three numbers matches in more than or equal to 6 draws. (Only thing
which is pending in this code is that it also lists duplicates which I
will remove later)

What I want more : Optimization
===============================

I have observed that on my computer (Intel Celeron 800 MHz 256MB SDRAM)
the total time it takes is 14:52 minutes to complete the code with the
criteria (Combinations A2=3, Matches B1=3 and frequency C1=6). If I
remove the conditional formatting in the range AC11:AL11 the total time
reduces to 12:45. If I remove the progress counter from the cell AI9
(by commenting the line combins = combins + 1 and Range("AI9").Value =
Format(combins / Range("AI7").Value, "00.00%")) then the time reduces
to 12:14. If I set the screen updating to FALSE, the total time reduces
considerably to 2:35 which is great.

Now I want you or somebody else to check my modified code to see if the
total time can be reduced more. I want this becuase when I want to
create combinations of 10 numbers, it should not consume unnecessary
time.

I have commented all lines prefexing it with '**

For i = 1 To UBound(ItemsChosen)
sValue = sValue & ", " & vAllItems(ItemsChosen(i), 1)
Range("AC1").Offset(0, z) = vAllItems(ItemsChosen(i), 1) '**
added to list each combinations
z = z + 1 '**
added in the rage AC1:AL1
Next i

'and save it in the buffer
z = 1 '**
added
combins = combins + 1
Range("AI9").Value = Format(combins / Range("AI7").Value, "00.00%") '**
added

If Range("AN1").Value >= Range("C1").Value Then '**
added ( adds to buffer only if the combination matches the criteria
[combinations, matches, frequency] )
BufferPtr = BufferPtr + 1
Buffer(BufferPtr) = Mid$(sValue, 3) & " > " & Range("AN1").Value '**
added (& " > " & Range("AN1").Value to find out the frequency)
End If

I am not sure if the approach/logic I followed is correct and need
feedback from VBA Experts like you. I have used a combination of VBA
and Excel formulas to fulfil my requirement. Please let me know if it
is correct and whether it can be changed to reduce more time and
improve on efficiency.

Maxi

Maxi wrote:
I am getting errors while submitting a reply and not sure if it went
through. Trying it again and it might appear twice.

I tried a lot but I am not getting a correct solution. I am still
trying to finish this. I would appreciate if you can help me with this.
For you it will be a cake walk.

.

Relevant Pages

  • Re: Tom Ogilvys VBA code
    ... I got hold of a vba code by Myrna Larson (July 25, 2000, ... Enter this in cell A2 ... which is pending in this code is that it also lists duplicates which I ... remove the conditional formatting in the range AC11:AL11 the total time ...
    (microsoft.public.excel.programming)
  • Re: Tom Ogilvys VBA code
    ... Enter this in cell A2 ... which is pending in this code is that it also lists duplicates which I ... remove the conditional formatting in the range AC11:AL11 the total time ... added (adds to buffer only if the combination matches the criteria ...
    (microsoft.public.excel.programming)
  • Re: Tom Ogilvys VBA code
    ... Maxi wrote: ... Enter this in cell A2 ... which is pending in this code is that it also lists duplicates which I ... remove the conditional formatting in the range AC11:AL11 the total time ...
    (microsoft.public.excel.programming)
  • Re: Tom Ogilvys VBA code
    ... Enter this in cell A2 ... which is pending in this code is that it also lists duplicates which I ... remove the conditional formatting in the range AC11:AL11 the total time ... added (adds to buffer only if the combination matches the criteria ...
    (microsoft.public.excel.programming)
  • Re: Tom Ogilvys VBA code
    ... Enter this in cell A2 ... I have modified this vba code and added two more criteria: ... which is pending in this code is that it also lists duplicates which I ... remove the conditional formatting in the range AC11:AL11 the total time ...
    (microsoft.public.excel.programming)