Get Row Number in Excel for given Column Number and Cell Data

Question

If I know the cell data and the column number where i expect the data to be in. Please let me know how do i retrieve the row number for that cell. Thank you.

Answer 1

Here is one way for column B:

Sub HappinessRow()

Dim r As Range

Set r = Range("B:B").Find(what:="happiness", after:=Range("B1"))
If r Is Nothing Then
    MsgBox "could not find happiness"
    Exit Sub
End If
MsgBox "happiness found in row " & r.Row
End Sub

EDIT#1:

This version uses parameters for the value to find, and the column to search:

Sub HappinessRow2()

    Dim r As Range, s As String, kolumn As Long

    s = "happiness"
    kolumn = 2

    Set r = Cells(1, kolumn).EntireColumn.Find(what:="happiness", after:=Cells(1, kolumn))
    If r Is Nothing Then
        MsgBox "could not find happiness"
        Exit Sub
    End If

    MsgBox "happiness found in row " & r.Row
End Sub