Forum Discussion
Can you enter a value in one cell and have it look up the same value in another cell?
- Jul 09, 2023
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.
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