Forum Discussion

Ed_Sanchez's avatar
Ed_Sanchez
Copper Contributor
Jul 07, 2023
Solved

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 tw...
  • SnowMan55's avatar
    Jul 09, 2023

    Ed_Sanchez 

    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.

     

Resources