Help Needed for Groups Please

I'm still at a loss.

maybe...

Option Explicit
Sub Combinations_626()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim oRow As Long
Dim oCol As Long
Dim myStr As String

Application.ScreenUpdating = False
With Worksheets.Add
oRow = 0
oCol = 1
For A = 1 To 21
For B = A + 1 To 22
For C = B + 1 To 23
For D = C + 1 To 24
For E = D + 1 To 25
For F = E + 1 To 26
myStr = "{" & A & "," & B & "," & C & "," & D _
& "," & E & "," & F & "}"
If checkVals(myStr) = True Then
oRow = oRow + 1
If oRow = 65001 Then
oRow = oRow + 1
oCol = oCol + 1
End If
.Cells(oRow, oCol).Value = A & "-" & B _
& "-" & C & "-" & D & "-" & E & "-" & F
End If
Next F
Next E
Next D
Next C
Next B
Next A
End With
Application.ScreenUpdating = True

End Sub

Function checkVals(StrIn As String) As Boolean

Dim KeyWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim GroupTotal(1 To 6) As Long
Dim myCounts(0 To 6) As Long
Dim iCtr As Long

Dim myRng As Range
Dim res As Long

Set KeyWks = Worksheets("keys")

With KeyWks
FirstRow = 1
LastRow = 6

For iRow = FirstRow To LastRow
Set myRng = .Range(.Cells(iRow, "A"), _
.Cells(iRow, .Columns.Count).End(xlToLeft))

res = Evaluate("Sum(CountIf(" & myRng.Address(external:=True) _
& "," & StrIn & "))")

GroupTotal(iRow) = res

Next iRow
End With

'now you can inspect those 6 values to see which ones
'give the values you want.
For iCtr = LBound(myCounts) To UBound(myCounts)
If GroupTotal(iCtr) = iCtr Then
myCounts(iCtr) = myCounts(iCtr) + 1
End If
Next iCtr

'now mycounts give you a count for each group.
'do what you have to do to flunk the stuff you don't want.

End Function

myCounts will be the count of the number of matches. So mycounts(4) will mean
that there were 4 groups that matched.

I don't think I know enough to offer any more help.

good luck,

Hi Dave,

Thanks Again.
I tried Running the Macro and it came up with a Run Time Error '9' (
Subscript Out of Range ) on Line :-
If GroupTotal(iCtr) = iCtr Then

I am a Newbie to VB and am Unable to Resolve this Error.

I put some Letters in a Sheet Named "keys" ( Cells A1:F1 ) and just
Ran the Sub "testme01". It came up saying that "myMsg" had Not Been
Defined. I Defined it as a Variant ( is that Right ? ) and Ran it
again. It worked Fine.
I will look through what you have done and Try to Adapt it ( after I
can understand it ) so I can Include it into my Original Program. That
way the Combinations will be Excluded Before they are Written to the
Active Sheet.

Thanks and All the Best
Paul



Dave Peterson wrote in message ...
I'm still at a loss.

maybe...

Option Explicit
Sub Combinations_626()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim oRow As Long
Dim oCol As Long
Dim myStr As String

Application.ScreenUpdating = False
With Worksheets.Add
oRow = 0
oCol = 1
For A = 1 To 21
For B = A + 1 To 22
For C = B + 1 To 23
For D = C + 1 To 24
For E = D + 1 To 25
For F = E + 1 To 26
myStr = "{" & A & "," & B & "," & C & "," & D _
& "," & E & "," & F & "}"
If checkVals(myStr) = True Then
oRow = oRow + 1
If oRow = 65001 Then
oRow = oRow + 1
oCol = oCol + 1
End If
.Cells(oRow, oCol).Value = A & "-" & B _
& "-" & C & "-" & D & "-" & E & "-" & F
End If
Next F
Next E
Next D
Next C
Next B
Next A
End With
Application.ScreenUpdating = True

End Sub

Function checkVals(StrIn As String) As Boolean

Dim KeyWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim GroupTotal(1 To 6) As Long
Dim myCounts(0 To 6) As Long
Dim iCtr As Long

Dim myRng As Range
Dim res As Long

Set KeyWks = Worksheets("keys")

With KeyWks
FirstRow = 1
LastRow = 6

For iRow = FirstRow To LastRow
Set myRng = .Range(.Cells(iRow, "A"), _
.Cells(iRow, .Columns.Count).End(xlToLeft))

res = Evaluate("Sum(CountIf(" & myRng.Address(external:=True) _
& "," & StrIn & "))")

GroupTotal(iRow) = res

Next iRow
End With

'now you can inspect those 6 values to see which ones
'give the values you want.
For iCtr = LBound(myCounts) To UBound(myCounts)
If GroupTotal(iCtr) = iCtr Then
myCounts(iCtr) = myCounts(iCtr) + 1
End If
Next iCtr

'now mycounts give you a count for each group.
'do what you have to do to flunk the stuff you don't want.

End Function

myCounts will be the count of the number of matches. So mycounts(4) will mean
that there were 4 groups that matched.

I don't think I know enough to offer any more help.

good luck,

This is one replacement:

For iCtr = LBound(GroupTotal) To UBound(GroupTotal)
If GroupTotal(iCtr) = iCtr Then
myCounts(iCtr) = myCounts(iCtr) + 1
End If
Next iCtr

Notice that the "for ictr" line changed to be based on grouptotal.

But I'm still not sure that's what you want.

And myMsg could have been declared as String
dim myMsg as String


Paul Black wrote:

Hi Dave,

Thanks Again.
I tried Running the Macro and it came up with a Run Time Error '9' (
Subscript Out of Range ) on Line :-
If GroupTotal(iCtr) = iCtr Then

I am a Newbie to VB and am Unable to Resolve this Error.

I put some Letters in a Sheet Named "keys" ( Cells A1:F1 ) and just
Ran the Sub "testme01". It came up saying that "myMsg" had Not Been
Defined. I Defined it as a Variant ( is that Right ? ) and Ran it
again. It worked Fine.
I will look through what you have done and Try to Adapt it ( after I
can understand it ) so I can Include it into my Original Program. That
way the Combinations will be Excluded Before they are Written to the
Active Sheet.

Thanks and All the Best
Paul

Dave Peterson wrote in message ...
I'm still at a loss.

maybe...

Option Explicit
Sub Combinations_626()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim oRow As Long
Dim oCol As Long
Dim myStr As String

Application.ScreenUpdating = False
With Worksheets.Add
oRow = 0
oCol = 1
For A = 1 To 21
For B = A + 1 To 22
For C = B + 1 To 23
For D = C + 1 To 24
For E = D + 1 To 25
For F = E + 1 To 26
myStr = "{" & A & "," & B & "," & C & "," & D _
& "," & E & "," & F & "}"
If checkVals(myStr) = True Then
oRow = oRow + 1
If oRow = 65001 Then
oRow = oRow + 1
oCol = oCol + 1
End If
.Cells(oRow, oCol).Value = A & "-" & B _
& "-" & C & "-" & D & "-" & E & "-" & F
End If
Next F
Next E
Next D
Next C
Next B
Next A
End With
Application.ScreenUpdating = True

End Sub

Function checkVals(StrIn As String) As Boolean

Dim KeyWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim GroupTotal(1 To 6) As Long
Dim myCounts(0 To 6) As Long
Dim iCtr As Long

Dim myRng As Range
Dim res As Long

Set KeyWks = Worksheets("keys")

With KeyWks
FirstRow = 1
LastRow = 6

For iRow = FirstRow To LastRow
Set myRng = .Range(.Cells(iRow, "A"), _
.Cells(iRow, .Columns.Count).End(xlToLeft))

res = Evaluate("Sum(CountIf(" & myRng.Address(external:=True) _
& "," & StrIn & "))")

GroupTotal(iRow) = res

Next iRow
End With

'now you can inspect those 6 values to see which ones
'give the values you want.
For iCtr = LBound(myCounts) To UBound(myCounts)
If GroupTotal(iCtr) = iCtr Then
myCounts(iCtr) = myCounts(iCtr) + 1
End If
Next iCtr

'now mycounts give you a count for each group.
'do what you have to do to flunk the stuff you don't want.

End Function

myCounts will be the count of the number of matches. So mycounts(4) will mean
that there were 4 groups that matched.

I don't think I know enough to offer any more help.

good luck,

--

Dave Peterson

This is what I really wanted to do:

For iCtr = LBound(GroupTotal) To UBound(GroupTotal)
myCounts(GroupTotal(iCtr)) = myCounts(GroupTotal(iCtr)) + 1
Next iCtr

After this loop completes, you'll now how many had 0 matches (mycounts(0)), how
many had 3 matches (mycounts(3)), to how many had 6 matches (mycounts(6)).



Dave Peterson wrote:

This is one replacement:

For iCtr = LBound(GroupTotal) To UBound(GroupTotal)
If GroupTotal(iCtr) = iCtr Then
myCounts(iCtr) = myCounts(iCtr) + 1
End If
Next iCtr

Notice that the "for ictr" line changed to be based on grouptotal.

But I'm still not sure that's what you want.

And myMsg could have been declared as String
dim myMsg as String

Paul Black wrote:

Hi Dave,

Thanks Again.
I tried Running the Macro and it came up with a Run Time Error '9' (
Subscript Out of Range ) on Line :-
If GroupTotal(iCtr) = iCtr Then

I am a Newbie to VB and am Unable to Resolve this Error.

I put some Letters in a Sheet Named "keys" ( Cells A1:F1 ) and just
Ran the Sub "testme01". It came up saying that "myMsg" had Not Been
Defined. I Defined it as a Variant ( is that Right ? ) and Ran it
again. It worked Fine.
I will look through what you have done and Try to Adapt it ( after I
can understand it ) so I can Include it into my Original Program. That
way the Combinations will be Excluded Before they are Written to the
Active Sheet.

Thanks and All the Best
Paul

Dave Peterson wrote in message ...
I'm still at a loss.

maybe...

Option Explicit
Sub Combinations_626()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim oRow As Long
Dim oCol As Long
Dim myStr As String

Application.ScreenUpdating = False
With Worksheets.Add
oRow = 0
oCol = 1
For A = 1 To 21
For B = A + 1 To 22
For C = B + 1 To 23
For D = C + 1 To 24
For E = D + 1 To 25
For F = E + 1 To 26
myStr = "{" & A & "," & B & "," & C & "," & D _
& "," & E & "," & F & "}"
If checkVals(myStr) = True Then
oRow = oRow + 1
If oRow = 65001 Then
oRow = oRow + 1
oCol = oCol + 1
End If
.Cells(oRow, oCol).Value = A & "-" & B _
& "-" & C & "-" & D & "-" & E & "-" & F
End If
Next F
Next E
Next D
Next C
Next B
Next A
End With
Application.ScreenUpdating = True

End Sub

Function checkVals(StrIn As String) As Boolean

Dim KeyWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim GroupTotal(1 To 6) As Long
Dim myCounts(0 To 6) As Long
Dim iCtr As Long

Dim myRng As Range
Dim res As Long

Set KeyWks = Worksheets("keys")

With KeyWks
FirstRow = 1
LastRow = 6

For iRow = FirstRow To LastRow
Set myRng = .Range(.Cells(iRow, "A"), _
.Cells(iRow, .Columns.Count).End(xlToLeft))

res = Evaluate("Sum(CountIf(" & myRng.Address(external:=True) _
& "," & StrIn & "))")

GroupTotal(iRow) = res

Next iRow
End With

'now you can inspect those 6 values to see which ones
'give the values you want.
For iCtr = LBound(myCounts) To UBound(myCounts)
If GroupTotal(iCtr) = iCtr Then
myCounts(iCtr) = myCounts(iCtr) + 1
End If
Next iCtr

'now mycounts give you a count for each group.
'do what you have to do to flunk the stuff you don't want.

End Function

myCounts will be the count of the number of matches. So mycounts(4) will mean
that there were 4 groups that matched.

I don't think I know enough to offer any more help.

good luck,

--

Dave Peterson


--

Dave Peterson

Hi Dave,

I think it is probably the way I am explaining what I am trying to
achieve that is causing the confusion.
I would really like to be able to do this.
Would it be a simpler solution to put the Groups into a Worksheet (
Named "Group Criteria" for example ) and then Modify the Macro ( I
posted ) I am using to lookup the Groups in the Worksheet ( Named
"Group Criteria ) and EXCLUDE Combinations that do NOT meet the
Criteria, so they are NOT written to the Worksheet ( Named
"Combinations" for example )?.

In a Sheet Named "Group Criteria" :-
Cell A1=Group 1, B1=1, C1=6, D1=11, E1=15, F1=19, G1=23
Cell A2=Group 2, B2=2, C2=7, D2=12, E2=16, F2=20, G2=24
Cell A3=Group 3, B3=3, C3=8, D3=13, E3=17, F3=21, G3=25
Cell A4=Group 4, B4=4, C4=9, D4=14, E4=18, F4=22, G4=26
Cell A5=Group 5, B5=5, C5=10

The Main Criteria I want is that there are NOT more than 4 Numbers in
ANY Combination of 6 Numbers from ANY ONE GROUP.
Doing it this way, would it be easier to set it up as a Function?.

BTW, I added the amendment you posted. When I ran it, it added a new
sheet but produced NO information.

Thanks Dave.
All the Best
Paul



Dave Peterson wrote in message ...
This is what I really wanted to do:

For iCtr = LBound(GroupTotal) To UBound(GroupTotal)
myCounts(GroupTotal(iCtr)) = myCounts(GroupTotal(iCtr)) + 1
Next iCtr

After this loop completes, you'll now how many had 0 matches (mycounts(0)), how
many had 3 matches (mycounts(3)), to how many had 6 matches (mycounts(6)).



Dave Peterson wrote:

This is one replacement:

For iCtr = LBound(GroupTotal) To UBound(GroupTotal)
If GroupTotal(iCtr) = iCtr Then
myCounts(iCtr) = myCounts(iCtr) + 1
End If
Next iCtr

Notice that the "for ictr" line changed to be based on grouptotal.

But I'm still not sure that's what you want.

And myMsg could have been declared as String
dim myMsg as String

Paul Black wrote:

Hi Dave,

Thanks Again.
I tried Running the Macro and it came up with a Run Time Error '9' (
Subscript Out of Range ) on Line :-
If GroupTotal(iCtr) = iCtr Then

I am a Newbie to VB and am Unable to Resolve this Error.

I put some Letters in a Sheet Named "keys" ( Cells A1:F1 ) and just
Ran the Sub "testme01". It came up saying that "myMsg" had Not Been
Defined. I Defined it as a Variant ( is that Right ? ) and Ran it
again. It worked Fine.
I will look through what you have done and Try to Adapt it ( after I
can understand it ) so I can Include it into my Original Program. That
way the Combinations will be Excluded Before they are Written to the
Active Sheet.

Thanks and All the Best
Paul

Dave Peterson wrote in message ...
I'm still at a loss.

maybe...

Option Explicit
Sub Combinations_626()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim oRow As Long
Dim oCol As Long
Dim myStr As String

Application.ScreenUpdating = False
With Worksheets.Add
oRow = 0
oCol = 1
For A = 1 To 21
For B = A + 1 To 22
For C = B + 1 To 23
For D = C + 1 To 24
For E = D + 1 To 25
For F = E + 1 To 26
myStr = "{" & A & "," & B & "," & C & "," & D _
& "," & E & "," & F & "}"
If checkVals(myStr) = True Then
oRow = oRow + 1
If oRow = 65001 Then
oRow = oRow + 1
oCol = oCol + 1
End If
.Cells(oRow, oCol).Value = A & "-" & B _
& "-" & C & "-" & D & "-" & E & "-" & F
End If
Next F
Next E
Next D
Next C
Next B
Next A
End With
Application.ScreenUpdating = True

End Sub

Function checkVals(StrIn As String) As Boolean

Dim KeyWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim GroupTotal(1 To 6) As Long
Dim myCounts(0 To 6) As Long
Dim iCtr As Long

Dim myRng As Range
Dim res As Long

Set KeyWks = Worksheets("keys")

With KeyWks
FirstRow = 1
LastRow = 6

For iRow = FirstRow To LastRow
Set myRng = .Range(.Cells(iRow, "A"), _
.Cells(iRow, .Columns.Count).End(xlToLeft))

res = Evaluate("Sum(CountIf(" & myRng.Address(external:=True) _
& "," & StrIn & "))")

GroupTotal(iRow) = res

Next iRow
End With

'now you can inspect those 6 values to see which ones
'give the values you want.
For iCtr = LBound(myCounts) To UBound(myCounts)
If GroupTotal(iCtr) = iCtr Then
myCounts(iCtr) = myCounts(iCtr) + 1
End If
Next iCtr

'now mycounts give you a count for each group.
'do what you have to do to flunk the stuff you don't want.

End Function

myCounts will be the count of the number of matches. So mycounts(4) will mean
that there were 4 groups that matched.

I don't think I know enough to offer any more help.

good luck,

--

Dave Peterson

That little portion at the end didn't do anything. It was a way to see how many
of each match were found.

But if you want to check if there are no more than 4 numbers in each match, you
could check to see if the 5th and 6th mycounts were greater than 0.




For iCtr = LBound(GroupTotal) To UBound(GroupTotal)
myCounts(GroupTotal(iCtr)) = myCounts(GroupTotal(iCtr)) + 1
Next iCtr

if mycounts(5) + mycounts(6) 0 then
'too many
checkvals = false
else
checkvals = true
end if

right at the bottom of the checkvals function.




Paul Black wrote:

Hi Dave,

I think it is probably the way I am explaining what I am trying to
achieve that is causing the confusion.
I would really like to be able to do this.
Would it be a simpler solution to put the Groups into a Worksheet (
Named "Group Criteria" for example ) and then Modify the Macro ( I
posted ) I am using to lookup the Groups in the Worksheet ( Named
"Group Criteria ) and EXCLUDE Combinations that do NOT meet the
Criteria, so they are NOT written to the Worksheet ( Named
"Combinations" for example )?.

In a Sheet Named "Group Criteria" :-
Cell A1=Group 1, B1=1, C1=6, D1=11, E1=15, F1=19, G1=23
Cell A2=Group 2, B2=2, C2=7, D2=12, E2=16, F2=20, G2=24
Cell A3=Group 3, B3=3, C3=8, D3=13, E3=17, F3=21, G3=25
Cell A4=Group 4, B4=4, C4=9, D4=14, E4=18, F4=22, G4=26
Cell A5=Group 5, B5=5, C5=10

The Main Criteria I want is that there are NOT more than 4 Numbers in
ANY Combination of 6 Numbers from ANY ONE GROUP.
Doing it this way, would it be easier to set it up as a Function?.

BTW, I added the amendment you posted. When I ran it, it added a new
sheet but produced NO information.

Thanks Dave.
All the Best
Paul

Dave Peterson wrote in message ...
This is what I really wanted to do:

For iCtr = LBound(GroupTotal) To UBound(GroupTotal)
myCounts(GroupTotal(iCtr)) = myCounts(GroupTotal(iCtr)) + 1
Next iCtr

After this loop completes, you'll now how many had 0 matches (mycounts(0)), how
many had 3 matches (mycounts(3)), to how many had 6 matches (mycounts(6)).



Dave Peterson wrote:

This is one replacement:

For iCtr = LBound(GroupTotal) To UBound(GroupTotal)
If GroupTotal(iCtr) = iCtr Then
myCounts(iCtr) = myCounts(iCtr) + 1
End If
Next iCtr

Notice that the "for ictr" line changed to be based on grouptotal.

But I'm still not sure that's what you want.

And myMsg could have been declared as String
dim myMsg as String

Paul Black wrote:

Hi Dave,

Thanks Again.
I tried Running the Macro and it came up with a Run Time Error '9' (
Subscript Out of Range ) on Line :-
If GroupTotal(iCtr) = iCtr Then

I am a Newbie to VB and am Unable to Resolve this Error.

I put some Letters in a Sheet Named "keys" ( Cells A1:F1 ) and just
Ran the Sub "testme01". It came up saying that "myMsg" had Not Been
Defined. I Defined it as a Variant ( is that Right ? ) and Ran it
again. It worked Fine.
I will look through what you have done and Try to Adapt it ( after I
can understand it ) so I can Include it into my Original Program. That
way the Combinations will be Excluded Before they are Written to the
Active Sheet.

Thanks and All the Best
Paul

Dave Peterson wrote in message ...
I'm still at a loss.

maybe...

Option Explicit
Sub Combinations_626()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim oRow As Long
Dim oCol As Long
Dim myStr As String

Application.ScreenUpdating = False
With Worksheets.Add
oRow = 0
oCol = 1
For A = 1 To 21
For B = A + 1 To 22
For C = B + 1 To 23
For D = C + 1 To 24
For E = D + 1 To 25
For F = E + 1 To 26
myStr = "{" & A & "," & B & "," & C & "," & D _
& "," & E & "," & F & "}"
If checkVals(myStr) = True Then
oRow = oRow + 1
If oRow = 65001 Then
oRow = oRow + 1
oCol = oCol + 1
End If
.Cells(oRow, oCol).Value = A & "-" & B _
& "-" & C & "-" & D & "-" & E & "-" & F
End If
Next F
Next E
Next D
Next C
Next B
Next A
End With
Application.ScreenUpdating = True

End Sub

Function checkVals(StrIn As String) As Boolean

Dim KeyWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim GroupTotal(1 To 6) As Long
Dim myCounts(0 To 6) As Long
Dim iCtr As Long

Dim myRng As Range
Dim res As Long

Set KeyWks = Worksheets("keys")

With KeyWks
FirstRow = 1
LastRow = 6

For iRow = FirstRow To LastRow
Set myRng = .Range(.Cells(iRow, "A"), _
.Cells(iRow, .Columns.Count).End(xlToLeft))

res = Evaluate("Sum(CountIf(" & myRng.Address(external:=True) _
& "," & StrIn & "))")

GroupTotal(iRow) = res

Next iRow
End With

'now you can inspect those 6 values to see which ones
'give the values you want.
For iCtr = LBound(myCounts) To UBound(myCounts)
If GroupTotal(iCtr) = iCtr Then
myCounts(iCtr) = myCounts(iCtr) + 1
End If
Next iCtr

'now mycounts give you a count for each group.
'do what you have to do to flunk the stuff you don't want.

End Function

myCounts will be the count of the number of matches. So mycounts(4) will mean
that there were 4 groups that matched.

I don't think I know enough to offer any more help.

good luck,

--

Dave Peterson


--

Dave Peterson