Find row number of matching value

Question

I've been trying several different methods of finding the row number of bingo (listed and separated by asterisks) but none seem to work. What am I doing wrong? In all instances I've tried looking both for Bingo and "Bingo".

Sub Find_Bingo()

Dim wb As Workbook
Dim ws As Worksheet
Dim FoundCell As Range
Set wb = ActiveWorkbook
Set ws = ActiveSheet

    Const WHAT_TO_FIND As String = "Bingo"

    Set FoundCell = ws.Range("A").Find(What:=WHAT_TO_FIND)
    If Not FoundCell Is Nothing Then
        MsgBox (WHAT_TO_FIND & " found in row: " & FoundCell.Row)
    Else
        MsgBox (WHAT_TO_FIND & " not found")
    End If

'************

    With Sheet1
        Set FoundCell = Cells.Find(What:=Bingo, After:=.Cells(1, 1), _
 LookIn:=xlValues, lookat:= xlPart, SearchOrder:=xlByRows, _ 
 SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    End With

'************

Set FoundCell = Sheets("Sheet1").Columns("E").Find(Bingo, _
ActiveSheet.Cells(2, 2), LookIn:=xlValue, lookat:=xlWhole)

'************

FoundCell = Range("A:M").Find(Bingo)

'************

FoundCell = Application.WorksheetFunction.Match(Bingo, Range("A1:A200"), 0)

'************

FoundCell = Worksheets("Sheet1").Columns(1).Find(Bingo).Row

'************

Range("A:A").Find(Bingo, Range("A1")).Row

'************

ActiveWorkbook.Worksheets("Sheet1").Columns(1).Find(Bingo).Select

'************
End Sub

Answer 1

For your first method change ws.Range("A") to ws.Range("A:A") which will search the entirety of column a, like so:

Sub Find_Bingo()

        Dim wb As Workbook
        Dim ws As Worksheet
        Dim FoundCell As Range
        Set wb = ActiveWorkbook
        Set ws = ActiveSheet

            Const WHAT_TO_FIND As String = "Bingo"

            Set FoundCell = ws.Range("A:A").Find(What:=WHAT_TO_FIND)
            If Not FoundCell Is Nothing Then
                MsgBox (WHAT_TO_FIND & " found in row: " & FoundCell.Row)
            Else
                MsgBox (WHAT_TO_FIND & " not found")
            End If
End Sub

For your second method, you are using Bingo as a variable instead of a string literal. This is a good example of why I add Option Explicit to the top of all of my code modules, as when you try to run the code it will direct you to this "variable" which is undefined and not intended to be a variable at all.

Additionally, when you are using With...End With you need a period . before you reference Cells, so Cells should be .Cells. This mimics the normal qualifying behavior (i.e. Sheet1.Cells.Find..)

Change Bingo to "Bingo" and change Cells to .Cells

With Sheet1
        Set FoundCell = .Cells.Find(What:="Bingo", After:=.Cells(1, 1), _
        LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    End With

If Not FoundCell Is Nothing Then
        MsgBox ("""Bingo"" found in row " & FoundCell.Row)
Else
        MsgBox ("Bingo not found")
End If

Update

In my

With Sheet1
    .....
End With

The Sheet1 refers to a worksheet's code name, not the name of the worksheet itself. For example, say I open a new blank Excel workbook. The default worksheet is just Sheet1. I can refer to that in code either with the code name of Sheet1 or I can refer to it with the index of Sheets("Sheet1"). The advantage to using a codename is that it does not change if you change the name of the worksheet.

Continuing this example, let's say I renamed Sheet1 to Data. Using Sheet1 would continue to work, as the code name doesn't change, but now using Sheets("Sheet1") would return an error and that syntax must be updated to the new name of the sheet, so it would need to be Sheets("Data").

In the VB Editor you would see something like this:

Notice how, even though I changed the name to Data, there is still a Sheet1 to the left. That is what I mean by codename.

The Data worksheet can be referenced in two ways:

Debug.Print Sheet1.Name
Debug.Print Sheets("Data").Name

Both should return Data

More discussion on worksheet code names can be found here.