Find. On other sheet

[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

 

[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

 

[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

 

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

 

[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

 

[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

 

[kirkm]

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

Understood. All sorted now, many thanks.

Cheers - Kirk