I don't like using after unless you have to . The problem is when it gets to
the end of the range it goes back to the beginning. If you start with a
match in the cell specified by the AFTER parameter the code will end at the
same cell and form a match.

For eample


A1 = 5
A2 = 6
A3 = 7

set c = Range("A1:A3").Find(what:=6, after:=Range("A2"))

c will return A2, not nothing. So you have to test for nothing and address
not equal to A2. Like this

set c = Range("A1:A3").Find(what:=6, after:=Range("A2"))
if not c is nothing and c.address < "A2"

" wrote:

On Mar 21, 3:57 am, kirkm wrote:
Hi,

I have this working -

Dim rngFound As Range

TheCol = "N"
With Range(theCol & ":" & theCol)
Set rngFound = .Find(What:=mt, after:=.Range("A1"),
LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByColumns,
searchdirection:=xlNext, MatchCase:=False, matchbyte:=False)
If Not rngFound Is Nothing Then
r = rngFound.Row
End If
End With

It Finds any mt in Column N. But only in the sheet that's open.
I can't quite make it work with a specific Sheet by name. I think
it goes into the Range line.

Could someone show me the syntax, please?

Thanks - Kirk

Kirk,

Here is a sample of some code that you should be able to reference for
your syntax.

Best,

Matt Herbert

Sub CustomFind()

Dim rngFound As Range
Dim strCol As String
Dim wks As Worksheet
Dim strMt As String
Dim rngLastCell As Range
Dim lngR As Long

strCol = "N"
strMt = "Kirk"

Set wks = Worksheets("Sheet2")
Set rngLastCell = Columns(strCol)
Set rngLastCell = rngLastCell.Cells(rngLastCell.Cells.Count)

With wks.Columns(strCol)
Set rngFound = .Find(What:=strMt, after:=rngLastCell, _
LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByColumns, _
searchdirection:=xlNext, MatchCase:=False, matchbyte:=False)

If Not rngFound Is Nothing Then
lngR = rngFound.Row
End If

End With

End Sub