Welcome Guest
Log In | Register )
You last visited January 23, 2017, 7:51 pm
All times shown are
Eastern Time (GMT-5:00)

Hits against combo in Excel

Topic closed. 16 replies. Last post 10 years ago by budward.

Page 2 of 2
PrintE-mailLink
time*treat's avatar - radar

United States
Member #13130
March 30, 2005
2171 Posts
Offline
Posted: July 14, 2007, 6:40 pm - IP Logged

Someone asked about making this a little more flexible, so I made a few changes to show the simpler way. I'd say print out both versions so you can compare side-by-side. I made the test & draw numbers different. It's just easier to see which variable is doing what, that way. Cool I bolded the changes. Now you only change the numbers at the top. As you can see, the more tricks you want it to do, the more code you're going to wind up with.

~~~ 

Sub Swed35c7__B()
  'by time*treat'
 
  'compares test set of 35C7 numbers against'
  'known 35C7 drawing and tallies subset counts'
  'for the test__set matching the draw__set'
  'no blanks between data, R1C1 cell format'
  'flexible endpoints'
 
  Dim draw__row As Integer, test__row As Integer
  Dim pool() As Integer, t As Integer
  Dim match As Integer, totals() As Integer
  Dim draw__col As Integer, test__col As Integer
 
  Const pool__size As Integer = 35
  Const draw__size As Integer = 7
  Const min__group As Integer = 4
  Const last__test__row As Integer = 20
  Const last__draw__row As Integer = 45
 
  ReDim pool(pool__size)
  ReDim totals(min__group To draw__size)
     
  For t = min__group To draw__size
    Sheets("testset").Cells(2, 9 + t - min__group).Value = t & "hits"
  Next t
 
  For test__row = 3 To last__test__row
    For t = 1 To pool__size: pool(t) = 0: Next t
    For t = min__group To draw__size: totals(t) = 0: Next t

    For test__col = 1 To draw__size
      pool(Sheets("testset").Cells(test__row, test__col).Value) = 1
    Next test__col

    For draw__row = 3 To last__draw__row
      match = 0
      For draw__col = 2 To 8
      If pool(Sheets("drawset").Cells(draw__row, draw__col).Value) = 1 Then match = match + 1
      Next draw__col
      If match >= min__group Then totals(match) = totals(match) + 1
    Next draw__row
   
    For t = min__group To draw__size
      Sheets("testset").Cells(test__row, 9 + t - min__group).Value = "*" & totals(t)
    Next t
  Next test__row
End Sub

In neo-conned Amerika, bank robs you.
Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

    Avatar

    United States
    Member #33295
    February 19, 2006
    699 Posts
    Offline
    Posted: July 14, 2007, 6:47 pm - IP Logged

    Thanks time*treat.

    Hyper See Ya!

    Bud

    Isabel, you are going to feel very silly when this turns out to be make-believe.