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
Any Help will be Greatly Appreciated.
Thanks in Advance.
All the Best
Paul
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!