Find Very First Occurence

Hello All,

I have a find issue.
I am looking at a entire column in excel. I want to do this:
Using find or match, whatever fast - find the very first occurrence of a string. For example: if my string I am looking for, is banana and "banana" repeats multiple times in this target column, then that find returns me only the VERY first banana string in that column

Is that possible?

Thanks

R
RayneAsked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
I assume the difference is that ByRows would do a left to right, top to bottom search whereas ByCol would do top to bottom, right to left. As for After…, I'm not sure but you could easily test that in simple sheet with just a few items.
0
 
RayneAuthor Commented:
I need VBA for this
0
 
Martin LissOlder than dirtCommented:
Dim lngLastRow as Long
Dim lngIndex As Long
' This assumes the data is in column 'A'
lngLastRow = Range("A65536").End(xlUp).Row

For lngIndex = 1 To lngLastRow
    ' '1' is column A
    If Cells(lngIndex, 1).Value = "banana" Then
        MsgBox "First one is in row " & lngIndex
        Exit Sub
    End If
End If
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
RayneAuthor Commented:
Hello Martin,

I don't prefer this method as I would be looping several times over 50000 rows and doing this, will slow this down as I have to do this for several files, one at a time :(
0
 
RayneAuthor Commented:
hence match or find
0
 
Martin LissOlder than dirtCommented:
Cells.Find(What:="banana", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
0
 
RayneAuthor Commented:
Martin

Thank you :)

Some verification:
SearchOrder:=xlByColumns, - or will it be xlByRows? Whats the difference between the two?

 After:=ActiveCell - if the activecell changes, would that effect the sequence of search like A1 or C5?

Thanks
0
 
RayneAuthor Commented:
Thank you Martin :)
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.