LinkBack URL
About LinkBacks
kkaistha 
I am trying to expedite the process of checking bingo numbers output from a random string against a pre-made bingo card. The way this system works is I have one file with all of the random number strings associated with the name of the person each string was output for. I then have to copy and paste each string into a separate worksheet and run a macro to check and see if the person won a bingo. I was wondering if there was any way to expedite this process. For example, if I could just run a single macro to check all strings associated with a name that generated a new spreadsheet (or even a text file) detailing who won and how close the losers were. Also, I am currently experiencing an issue where the bingo checker is unclear as to where the matches have occurred (for example, the output will say "someone matched 4 on row 9 with 58," when there is no row 9).
In the Bingo Numbers workbook, I have included the bingo score card and the ways someone can win. The other workbook is the bingo checker with the macro I have been using, I have been copying and pasting in names and the individual strings then hitting run.
Here is the code:
Thank you for the help.
Public results_msg As String Public y As Integer Public FNLN As String Public LD_name As String Public bingo_scores As String Public UserName As String Public bscore_array(5) As Integer Public test_string As String Public bingo_Values(5) As String Public player_name As String Public found_count As String Public bingo_lane As Integer Sub read_player_data() Sheets("bingo_values").Select ' number to read Range("c1").Select players_to_process = ActiveCell.Value Dim myfile As String myfile = "C:\Users\xj3000\Desktop\score_output" & Format(Now(), "yyyymmdd hhmmss") & ".txt" Open myfile For Output As #1 Dim a As Integer For a = 2 To players_to_process + 1 results_msg = "" Range("A" & a).Select player_name = ActiveCell.Value Range("b" & a).Select test_string = ActiveCell.Value ' MsgBox player_name & " " & test_string & " step " & a Call control_test_loop ' MsgBox "a = " & a If results_msg = "" Then results_msg = "Nothing to report" End If Range("c" & a).Select ActiveCell.Value = results_msg Next a Close #1 End Sub Sub control_test_loop() ' test_string = "**1**14**29**21**63**61**55**72**23**54**65**61**19**50**11**44**6**64**60**51**69**12**33**31**11**67**57**38**19**58**13**7**65**20**68" ' test_string = "**3**12**11**2**6**61**55**72**23**54**65**61**19**50**11**44**6**64**60**51**69**12**33**31**11**67**57**38**19**58**13**7**65**20**68" For y = 1 To 12 ' compare the test string with each of the 11 possible bingo runs If y = 1 Then bingo_lane = 1 found_count = 0 bingo_Values(1) = 3 bingo_Values(2) = 23 bingo_Values(3) = 44 bingo_Values(4) = 55 bingo_Values(5) = 70 Call test_bingo_results ' Write #1, "found count = " & " for bingo_lane " & bingo_lane & " " & found_count End If ' If y = 2 Then bingo_lane = 2 found_count = 0 bingo_Values(1) = 12 bingo_Values(2) = 28 bingo_Values(3) = 40 bingo_Values(4) = 50 bingo_Values(5) = 64 Call test_bingo_results ' Write #1, "found count = " & " for bingo_lane " & bingo_lane & " " & found_count End If If y = 3 Then bingo_lane = 3 found_count = 0 bingo_Values(1) = 11 bingo_Values(2) = 18 ' bingo_values(3) = found_count = found_count + 1 ' bingo_Values(4) = 57 bingo_Values(5) = 75 Call test_bingo_results ' Write #1, "found count = " & " for bingo_lane " & bingo_lane & " " & found_count End If If y = 4 Then bingo_lane = 4 found_count = 0 bingo_Values(1) = 2 bingo_Values(2) = 25 bingo_Values(3) = 35 bingo_Values(4) = 47 bingo_Values(5) = 69 Call test_bingo_results ' Write #1, "found count = " & " for bingo_lane " & bingo_lane & " " & found_count End If If y = 5 Then bingo_lane = 5 found_count = 0 bingo_Values(1) = 6 bingo_Values(2) = 24 bingo_Values(3) = 37 bingo_Values(4) = 58 bingo_Values(5) = 74 Call test_bingo_results ' Write #1, "found count = " & " for bingo_lane " & bingo_lane & " " & found_count End If If y = 6 Then bingo_lane = 6 found_count = 0 bingo_Values(1) = 3 bingo_Values(2) = 12 bingo_Values(3) = 11 bingo_Values(4) = 2 bingo_Values(5) = 6 Call test_bingo_results ' Write #1, "found count = " & " for bingo_lane " & bingo_lane & " " & found_count End If If y = 7 Then bingo_lane = 7 found_count = 0 bingo_Values(1) = 23 bingo_Values(2) = 28 bingo_Values(3) = 18 bingo_Values(4) = 25 bingo_Values(5) = 24 Call test_bingo_results ' Write #1, "found count = " & " for bingo_lane " & bingo_lane & " " & found_count End If If y = 8 Then bingo_lane = 8 found_count = 0 bingo_Values(1) = 44 bingo_Values(2) = 40 found_count = 1 bingo_Values(4) = 35 bingo_Values(5) = 37 Call test_bingo_results ' Write #1, "found count = " & " for bingo_lane " & bingo_lane & " " & found_count End If If y = 9 Then bingo_lane = 9 found_count = 0 bingo_Values(1) = 55 bingo_Values(2) = 50 bingo_Values(3) = 57 bingo_Values(4) = 47 bingo_Values(5) = 58 Call test_bingo_results ' Write #1, "found count = " & " for bingo_lane " & bingo_lane & " " & found_count End If If y = 10 Then bingo_lane = 10 found_count = 0 bingo_Values(1) = 70 bingo_Values(2) = 64 bingo_Values(3) = 75 bingo_Values(4) = 69 bingo_Values(5) = 74 Call test_bingo_results ' Write #1, "found count = " & " for bingo_lane " & bingo_lane & " " & found_count End If If y = 11 Then bingo_lane = 11 found_count = 0 bingo_Values(1) = 3 bingo_Values(2) = 28 found_count = 1 bingo_Values(4) = 47 bingo_Values(5) = 74 Call test_bingo_results ' Write #1, "found count = " & " for bingo_lane " & bingo_lane & " " & found_count End If If y = 12 Then bingo_lane = 12 found_count = 0 bingo_Values(1) = 70 bingo_Values(2) = 50 found_count = 1 bingo_Values(4) = 25 bingo_Values(5) = 6 Call test_bingo_results ' Write #1, "found count = " & " for bingo_lane " & bingo_lane & " " & found_count End If Next y End Sub Sub test_bingo_results() Dim x As Integer Dim found_pos As Integer ' found_count = 0 For x = 1 To 5 Dim test_value As String test_value = "*" & bingo_Values(x) & "*" found_pos = 0 found_pos = InStr(1, test_string, test_value) ' Write #1, test_value & " position = " & found_pos If found_pos > 0 Then found_count = found_count + 1 If found_count = 4 Then Write #1, player_name & " matched " & found_count & " on row " & bingo_lane & " with " & bingo_Values(x) results_msg = player_name & " matched " & found_count & " on row " & bingo_lane & " with " & bingo_Values(x) End If If found_count = 5 Then MsgBox "OMG BINGO for " & player_name & " on row " & bingo_lane results_msg = results_msg & " , " & "OMG BINGO for " & player_name & " on row " & bingo_lane Write #1, "OMG BINGO for " & player_name & " on row " & bingo_lane Write #1, test_string ' Exit Sub End If End If ' MsgBox "found count = " & found_count & " bingo values " & bingo_values(x) Next x ' MsgBox "in test_bingo_results" End Sub
Register To Reply
Bookmarks