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.
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.
Thank you very much for your quick response. I Will try your code suggestions today.
Ed