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


" 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 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



" 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