Find. On other sheet

K

kirkm

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
 
J

Joel

You don't need the after and the other parameters I eliminated.

Dim rngFound As Range

TheCol = "N"
With Sheets("Sheet1").Range(theCol & ":" & theCol)
Set rngFound = .Find(What:=mt, _
LookIn:=xlValues, _
LookAt:=xlPart)

If Not rngFound Is Nothing Then
r = rngFound.Row
End If
End With
 
M

meh2030

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
 
J

Joel

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"
 
K

kirkm

Thanks to you both, you've given me lots of ideas
and I have got it working nicely, including a loop that
looks for more than one instance by updating the range
values.

I just wonder - what if you wanted to check 2 adjacent
columns ?

This fails (so obvously isn't right but I can't think of an
alternative, apart from searching twice)

theCol = "S2:T100"
mt = "RowSRowT"
Do

With Sheets("Sheet1").Range(theCol)
Set rngFound = .Find(What:=mt, _
LookIn:=xlValues, _
LookAt:=xlWhole)

If Not rngFound Is Nothing Then
r = rngFound.Row
End If
End With

I've set up this test condition

Cells(10,"S") = "RowS"
Cells(10,"T") = "RowT"

Thanks - Kirk
 
J

Joel

You are looking for two different string of text. What you did won't work

SearchStr = array("RowS",RowT")

theCol = "S2:T100"

for each MyStr in SearchStr
With Sheets("Sheet1").Range(theCol)
Set rngFound = .Find(What:=MyStr, _
LookIn:=xlValues, _
LookAt:=xlWhole)

If Not rngFound Is Nothing Then
r = rngFound.Row
End If
End With
Next MyStr
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top