[RESOLVED] Quickie simple question

[Aelanna]

I'm sure by now everybody is tired of me and my questions, but this one I'm sure is simple and quick, but I've been unable to find exactly the answer in searches of this forum and others.

How can I search a particular column of cells for a certain value and get the row number of where that value is?

i.e. search column B for a cell with "apple" in it, and get the row of where it is.

I'm thinking worksheetfunction.find, maybe? Although I don't know how to use that, and I'm not sure that's what I need.

[NeedSomeAnswers]

Yes you can do this

1, Declare a Range on the column - Select a Range of Cells - e.g. A1 TO A100

go to - Insert / Name / Define - and give it a name e.g. - "SearchValues" .

Then -

Loop through the Range e.g -

Sub Test ()

Dim vSearch as String

vSearch = Search("apple")

msgbox "Row count is " & vSearch

end sub

Private function Search(strSearchVal as String) as Long

Dim RowCount as String
Dim n as String

n = 1

For Each cell In Range("SearchValues")
If cell.Value = strSearchVal then
RowCount = n
Exit For
End If
n = n + 1
Next

Search = RowCount

End Function

[Aelanna]

I actually ended up finding a function that will do what I want for this,
iRow = Range("C:C").Find(gstrNam, , , xlWhole).Row

That works ok to find ONE instance of something, where the something is unique in that column. But what if I want to find the LAST occurence of something in a column where there are multiple occurences?

[DKenny]

You cound write a new function, like Find, but working backwards through the cells in your range.

Function FindLast(fValue As String, fRange As Range) As Long
Dim x As Long
    
    If fRange.Columns.Count <> 1 Then
        MsgBox "fRange should have only one column."
        FindLast = -1
        Exit Function
    End If
    
    For x = fRange.Rows.Count To 1 Step -1
        If fRange.Cells(x, 1).Value = fValue Then
            FindLast = fRange.Cells(x, 1).Row
            Exit Function
        End If
    Next x
    
    FindLast = 0
End Function