Forum Discussion
Ed_Sanchez
Jul 07, 2023Copper Contributor
Can you enter a value in one cell and have it look up the same value in another cell?
I have a project for a bingo game. I want to mirror my laptop to a 70" TV set to display a Bingo Master number call sheet. I have an attached picture of what I want. Also attached is a copy, in two parts, of the macro generated VBA code.
What I want is to enter a number, when a number is called. I want the macro to find that value in another cell and shade it. The master sheet will record all the numbers called and will be used to validate a winning bingo card.
Any help will be greatly appreciated.
Ed
It is best to capture the G1 and J1 cell values into a variable (or into two variables, if you prefer), and then use that variable as an argument to the Find functions. Similarly, for cell content validation, it is best to capture the found cell range into a variable.
Sub Bingo_Call() ' Bingo_Call Macro ' All cells are formatted as GENERAL. Dim strCellContent As String Dim rngFoundCell As Range '---- Highlight the cell in the Bingo Numbers Called section that ' contains the Bingo column. strCellContent = Range("G1").Value ' Set rngFoundCell = Cells.Find(What:=strCellContent, After:=Range("A3") _ , LookIn:=xlFormulas2, LookAt:=xlWhole, SearchDirection:=xlNext _ , MatchCase:=True) If rngFoundCell.Address = "$G$1" Then Call MsgBox("Invalid Bingo column!", vbExclamation) Exit Sub End If ' With rngFoundCell.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorLight2 .TintAndShade = 0.799981688894314 .PatternTintAndShade = 0 End With '---- Highlight the cell in the Bingo Numbers Called section that ' contains the Bingo number. strCellContent = Range("J1").Value ' Set rngFoundCell = Cells.Find(What:=strCellContent, After:=Range("A3") _ , LookIn:=xlFormulas2, LookAt:=xlWhole, SearchDirection:=xlNext _ , MatchCase:=True) If rngFoundCell.Address = "$J$1" Then Call MsgBox("Invalid Bingo number!", vbExclamation) Exit Sub End If ' With rngFoundCell.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorLight2 .TintAndShade = 0.799981688894314 .PatternTintAndShade = 0 End With End Sub
Note that this code does not cross-validate the Bingo column letter against the Bingo number (e.g., to detect the invalid combination N 46). That validation would require an additional variable, some code rearrangement, and an additional test.
Alternatively (and maybe you have already done this), G1 can be populated with a formula that calculates the Bingo column letter based on the Bingo number; e.g.:=CHOOSE( CEILING.MATH(J1/15), "B","I","N","G","O" )
That also prevents the user from entering a lower-case column letter, which would be a problem because your Find functions specify a case-sensitive search.
- SnowMan55Bronze Contributor
It is best to capture the G1 and J1 cell values into a variable (or into two variables, if you prefer), and then use that variable as an argument to the Find functions. Similarly, for cell content validation, it is best to capture the found cell range into a variable.
Sub Bingo_Call() ' Bingo_Call Macro ' All cells are formatted as GENERAL. Dim strCellContent As String Dim rngFoundCell As Range '---- Highlight the cell in the Bingo Numbers Called section that ' contains the Bingo column. strCellContent = Range("G1").Value ' Set rngFoundCell = Cells.Find(What:=strCellContent, After:=Range("A3") _ , LookIn:=xlFormulas2, LookAt:=xlWhole, SearchDirection:=xlNext _ , MatchCase:=True) If rngFoundCell.Address = "$G$1" Then Call MsgBox("Invalid Bingo column!", vbExclamation) Exit Sub End If ' With rngFoundCell.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorLight2 .TintAndShade = 0.799981688894314 .PatternTintAndShade = 0 End With '---- Highlight the cell in the Bingo Numbers Called section that ' contains the Bingo number. strCellContent = Range("J1").Value ' Set rngFoundCell = Cells.Find(What:=strCellContent, After:=Range("A3") _ , LookIn:=xlFormulas2, LookAt:=xlWhole, SearchDirection:=xlNext _ , MatchCase:=True) If rngFoundCell.Address = "$J$1" Then Call MsgBox("Invalid Bingo number!", vbExclamation) Exit Sub End If ' With rngFoundCell.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorLight2 .TintAndShade = 0.799981688894314 .PatternTintAndShade = 0 End With End Sub
Note that this code does not cross-validate the Bingo column letter against the Bingo number (e.g., to detect the invalid combination N 46). That validation would require an additional variable, some code rearrangement, and an additional test.
Alternatively (and maybe you have already done this), G1 can be populated with a formula that calculates the Bingo column letter based on the Bingo number; e.g.:=CHOOSE( CEILING.MATH(J1/15), "B","I","N","G","O" )
That also prevents the user from entering a lower-case column letter, which would be a problem because your Find functions specify a case-sensitive search.
- Ed_SanchezCopper ContributorTHANK YOU VERY, VERY MUCH. I tried to copy and paste it again, and it worked this time. Don't know what happened last time, but everything is working the way I wanted it to. To make this worse I typed the code in myself and made mistakes.
AGAIN, THANK YOU VERY, VERY MUCH. I will add some validations to. the code as you suggested.
Ed - Ed_SanchezCopper Contributor
Hi, I tried your code. But, I had to enter it manually because I couldn't copy and paste it into my macro. The macro found two errors. I hope I typed the code in correctly. I checked it several times and I think it mirrors what you sent me.
I have the two errors commented and highlighted, as error 1 and 2. See below.
As I said in my profile, I was a COBOL and VAX Basic programmer for 12 years but I haven't written any code in 25 years and I don't know VBA.
I thank you for the help you already offered. I tried debugging the code with my very limited knowledge of VBA but there is a whole lot more that I need know.
And any help you can extend to me will again be greatly apprecheated.
ED
- Ed_SanchezCopper ContributorI forgot to tell you that the VBA I am using is from Excel 365. I don't if it makes any difference to what your may be using.
Ed
- Ed_SanchezCopper ContributorThank you very much. I will try your code suggestions. I also thought I would need a valuable but did not know how to pass it to the argument in the VBA code.
Thank you very much for your quick response. I Will try your code suggestions today.
Ed