Loop through an entire column to find a value

Question

How can I loop through all the rows of a column in VBA ?

Here is my sample :

Function FindRow(Item As String, Rng As range) As Variant

Dim row As Integer
Dim col As Integer

For col = 1 To Rng.Columns.Count
    For row = 1 To Rng.Rows.Count        
        If Rng.Cells(row, col).Value = Item Then
            FindRow = row + Rng.row - 1
            Exit Function
        End If
    Next row
Next col

FindRow = CVErr(XlCVError.xlErrNA)

End Function

If my range is a single column, Rng.Columns.Count is 1 and Rng.Rows.Count is 1048576. My function stops before entering the second For. If I add a watch on Rng.Cells(row, col).Value, I got an

<Application-defined or object-defined error> 

in the watch window, but no pop-up.

Thank you.

EDIT

Second solution

Using Rng.Find(Item).Row

Function FindRow(Item As String, Rng As range) As Variant

FindRow = Rng.Find(Item).Row

End Function

/!\ It returns #VALUE instead of #N/A if the Item is not in the range

Answer 1

Why not just use the built-in MATCH function?

=MATCH(C3,A:A,0)

---

... because you want to search many columns at once. To do so, you can use this UDF:

Function FindRow(lookFor As String, rng As Range)
    Dim v As Variant
    Dim iRow As Long
    Dim iCol As Long

    'Read contents of range into a Variant array
    v = rng.Value 

    'Loop through contents to locate the desired element
    For iRow = LBound(v, 1) To UBound(v, 1)
        For iCol = LBound(v, 2) To UBound(v, 2)
            If v(iRow, iCol) = lookFor Then
                FindRow = iRow
                Exit Function
            End If
        Next iCol
    Next iRow



    FindRow = CVErr(XlCVError.xlErrNA)
End Function