Hello All,

I have the 6 winning numbers in a history file in the format:
3,6,13,22,31,32
10,17,25,30,33,36

I also have the numbers we played in another location in the same
format. (Same file)
The formulas I tried became unmanagable and I could not finish.

Q: Does anyone have a bit of code/macro that will check our numbers
against the history and report if there are 4,5 matches? That is,
tell us if our numbers have won before.

I can modify my data format to suit your code if need be.

Thanks for any help.

Greer

Try this code. The number of 4 matches will be put into the column next to
the numbers you play. The 5 matches in the next column and the 6 matches in
the 3rd column


Sub lottery()

Set Winners = Sheets("Sheet1").Range("A1:A200")

Set OurNumbers = Sheets("Sheet2").Range("A1:A100")

For Each OurNum In OurNumbers
Match4 = 0
Match5 = 0
Match6 = 0

OurArray = Split(OurNum, ",")
For Each WinNum In Winners

WinArray = Split(WinNum, ",")
Matches = 0
For i = LBound(OurArray) To UBound(OurArray)
For j = LBound(WinNum) To UBound(WinNum)
If OurArray(i) = WinNum(j) Then
Matches = Matches + 1
Exit For
End If
Next j
Next i
Select Case Matches

Case 4: Match4 = Match4 + 1
Case 5: Match5 = Match5 + 1
Case 6: Match6 = Match6 + 1
End Select

Next WinNum
OutNum.Offset(0, 1) = Match4
OutNum.Offset(0, 2) = Match5
OutNum.Offset(0, 3) = Match6

Next OurNum

End Sub


"(E-Mail Removed)" wrote:

> Hello All,
>
> I have the 6 winning numbers in a history file in the format:
> 3,6,13,22,31,32
> 10,17,25,30,33,36
>
> I also have the numbers we played in another location in the same
> format. (Same file)
> The formulas I tried became unmanagable and I could not finish.
>
> Q: Does anyone have a bit of code/macro that will check our numbers
> against the history and report if there are 4,5 matches? That is,
> tell us if our numbers have won before.
>
> I can modify my data format to suit your code if need be.
>
> Thanks for any help.
>
> Greer
>

On Oct 14, 10:29*am, Joel <J...@discussions.microsoft.com> wrote:
> Try this code. *The number of 4 matches will be put into the column next to
> the numbers you play. *The 5 matches in the next column and the 6 matches in
> the 3rd column
>
> Sub lottery()
>
> Set Winners = Sheets("Sheet1").Range("A1:A200")
>
> Set OurNumbers = Sheets("Sheet2").Range("A1:A100")
>
> For Each OurNum In OurNumbers
> * *Match4 = 0
> * *Match5 = 0
> * *Match6 = 0
>
> * *OurArray = Split(OurNum, ",")
> * *For Each WinNum In Winners
>
> * * * WinArray = Split(WinNum, ",")
> * * * Matches = 0
> * * * For i = LBound(OurArray) To UBound(OurArray)
> * * * * *For j = LBound(WinNum) To UBound(WinNum)
> * * * * * * If OurArray(i) = WinNum(j) Then
> * * * * * * * *Matches = Matches + 1
> * * * * * * * *Exit For
> * * * * * * End If
> * * * * *Next j
> * * * Next i
> * * * Select Case Matches
>
> * * * * *Case 4: Match4 = Match4 + 1
> * * * * *Case 5: Match5 = Match5 + 1
> * * * * *Case 6: Match6 = Match6 + 1
> * * * End Select
>
> * *Next WinNum
> * *OutNum.Offset(0, 1) = Match4
> * *OutNum.Offset(0, 2) = Match5
> * *OutNum.Offset(0, 3) = Match6
>
> Next OurNum
>
> End Sub
>
>
>
> "greerjohn2...@gmail.com" wrote:
> > Hello All,
>
> > I have the 6 winning numbers in a history file in the format:
> > 3,6,13,22,31,32
> > 10,17,25,30,33,36
>
> > I also have the numbers we played in another location in the same
> > format. (Same file)
> > The formulas I tried became unmanagable and I could not finish.
>
> > Q: *Does anyone have a bit of code/macro that will check our numbers
> > against the history and report if there are 4,5 matches? *That is,
> > tell us if our numbers have won before.
>
> > I can modify my data format to suit your code if need be.
>
> > Thanks for any help.
>
> > Greer- Hide quoted text -
>
> - Show quoted text -

Hello,

First up, thank you for your help.

I tried using the code but keep getting the error: Runtime error 13.
Type mismatch
On debugging it points to this line:

For j = LBound(WinNum) To UBound(WinNum)

I tried all I knew, including copying some of the winning numbers
directly into OurNumbers (Sheet2 A1:A100)

Any suggestions?

Thanks again,

greer