Sorry Guys,
I Don't Know why the Above Post Got Screwed Up, I have had Another Go
Below.
Hi Tim,
Max is Exactly Right ( Thanks Max ), it is for the Bonus Ball in a
Lotto, I should have Mentioned that. The Formula Works, But for ALL
Cases Creates such a Big Excel File it is VERY Slow and Unmanageable. My
thoughts were that Using a Macro to Achieve this would be a Lot Faster
Because it would ONLY put the Values in the Cells as Opposed to having
Formulas in the Cells.
Thanks Very Much in Advance.
All the Best.
Paul
Paul Black wrote in message
...
Hi Tim,
You are Quite Right.
This is Actually the Second Time I have Posted this Request over the
Last Couple of Months.
I have Included Below the Original Message Along with my Feeble
Macro
Attempt to Arrive at a Solution :-
Hi Everyone,
I have a List of 6 Numbers in Cells E9:J1009.
I have an Extra Number in Cells K9:K1009.
I have a List of 6 Numbers that I want to Get a Total Matched for in
Cells M9:R109.
If I was to Use an Excel Formula, this would do what I want for the
First 6 Numbers to Check :-
=IF($M$9=0,""),IF(SUMPRODUCT(COUNTIF($M$9:$R$9,$E9 :$J9))=5,IF(SUMPRODUCT
(COUNTIF($M$9:$R$9,$K9))=1,"5+","5"),SUMPRODUCT(CO UNTIF($M$9:$R$9,$E9:$J
9))))
I would Ideally like a Macro that takes the First Set of 6 Numbers
in
Cells "M9:R9", and Uses Something Like the Formula Above to Give the
Value of the Total Numbers Matched in Cells "M9:R9" with the Numbers
in
Cells "E9:K9" and Put the Value in Cell "T9". Then Carry on Using
the
Same 6 Numbers in Cells "M9:R9" But Match with Cells "E10:K10" and
Put
the Value in Cell "T10". Then Continue this Process Until it Runs
Out of
Numbers in the Range "E9:K1009".
Then Repeat the Above Process with the Numbers in Cells "M10:R10",
and
Put the Value in Cell "U9", "U10", "U11" etc, then Use the Numbers
in
Cells "M11:R11", and Put the Value in Cell "V9", "V10", "V11" etc,
Until
there are NO More numbers to Check in Cells "M9:R109".
I Know it Probably Needs Something Like 2 Loops, and Using Cell
Offset
to the Right for Producing the Results. So the First Set of 6
Numbers
Results will go from "T9: Whatever", the Second Set of 6 Numbers
Results
will go from Cells "U9: Whatever", the Third Set of 6 Numbers
Results
will go from Cells "V9: Whatever" etc.
I have Come Up with the Following.
Is this the Right Approach for this Problem Or have I got it
Completely
Wrong.
I Don't Know How to Include the .
=IF($M$9=0,""),IF(SUMPRODUCT(COUNTIF($M$9:$R$9,$E9 :$J9))=5,IF(SUMPRODUCT
(COUNTIF($M$9:$R$9,$K9))=1,"5+","5"),SUMPRODUCT(CO UNTIF($M$9:$R$9,$E9:$J
9))))
. Formula which is the Bones of the Macro Working, Or How to
Accommodate
the Extra Number in Cells K9:K1009.
Perhaps Someone would be Kind Enough to Let me Know if I am on the
Right
Lines Or Not Please.
Sub Matched()
Dim GroupA(1000, 6) As Integer
Dim GroupB(200, 6) As Integer
Dim i As Integer
Dim j As Integer
Application.ScreenUpdating = False
Sheets("Data").Select
Range("E9:J1009").Select
i = 1
Do While ActiveCell.Value < ""
GroupA(i, 1) = ActiveCell.Offset(0, 0).Value
GroupA(i, 2) = ActiveCell.Offset(0, 1).Value
GroupA(i, 3) = ActiveCell.Offset(0, 2).Value
GroupA(i, 4) = ActiveCell.Offset(0, 3).Value
GroupA(i, 5) = ActiveCell.Offset(0, 4).Value
GroupA(i, 6) = ActiveCell.Offset(0, 5).Value
i = i + 1
ActiveCell.Offset(1, 0).Select
Loop
Sheets("Data").Select
Range("M9:R209").Select
j = 1
Do While ActiveCell.Value < ""
GroupB(j, 1) = ActjveCell.Offset(0, 0).Value
GroupB(j, 2) = ActjveCell.Offset(0, 1).Value
GroupB(j, 3) = ActjveCell.Offset(0, 2).Value
GroupB(j, 4) = ActjveCell.Offset(0, 3).Value
GroupB(j, 5) = ActjveCell.Offset(0, 4).Value
GroupB(j, 6) = ActjveCell.Offset(0, 5).Value
j = j + 1
ActiveCell.Offset(1, 0).Select
Loop
Sheets("Data").Select
Range("T9").Select
For i = 1 To 1000
For j = 1 To 200
ActiveCell.Offset(0, 1).Value = GroupA(i)
ActiveCell.Offset(1, 0).Select
Next j
Next i
Application.ScreenUpdating = True
End Sub
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!