Sorry I did not comment on the efficiency. The difference would be at best
negligible. Using with statements speeds things up if you are refering to the
same object over and over. If not then there is not much diffference.
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

The difference is in how you qualify your Cells. In the first instance you use
(.Cells(FmRow, Col), .Cells(ToRow, Col))
Which contains the dot. This means that the Cells reference is tied back to
the with statement. In the second instance you do not use the dot. This means
that Cells refers to the active sheet. You are trying to make a range on
sheet Ws out of 2 ranges on the active sheet. If Ws is the active sheet then
no problem. If not then the code will crash...

First instance is the same as
Set It = Ws.Range(Ws.Cells(FmRow, Col), Ws.Cells(ToRow, Col)) _
.Find(sLookFor, LookIn:=xlValues, LookAt:=WhoOrPrt)

Second instance is the same as
Set It = Ws.Range(Activesheet.Cells(FmRow, Col), Activesheet.Cells(ToRow,
Col)) _
.Find(sLookFor, LookIn:=xlValues, LookAt:=WhoOrPrt)


--
HTH...

Jim Thomlinson


"Neal Zimm" wrote:

Hi All -
I'm building a function to do a find.
the "A" code below works. The "B" code does NOT, yet it's closer to MSo
example. I can't see the diff. Help?
"B" would be more efficient, yes ?
Thanks.

Function zFinds_ByRin1C(Ws As Worksheet, sLookFor As String, _
Col As Integer, FmRow As Long, ToRow As Long, _
bXlWhole As Boolean) As Long
Dim It As Range, WhoOrPrt
If bXlWhole = True Then WhoOrPrt = xlWhole Else WhoOrPrt = xlPart
If FmRow < 1 Then FmRow = 1
If ToRow < 1 Or ToRow MSoMaxRow Then ToRow = MSoMaxRow

With Ws ' A start
Set It = .Range(.Cells(FmRow, Col), .Cells(ToRow, Col)) _
.Find(sLookFor, LookIn:=xlValues, LookAt:=WhoOrPrt)
End With ' A end

' B start returns not found 0 value with same data in the worksheet.
'With Ws.Range(Cells(FmRow, Col), Cells(ToRow, Col))
' Set It = .Find(sLookFor, LookIn:=xlValues, LookAt:=WhoOrPrt)
'End With
' B end

If Not It Is Nothing Then zFinds_ByRin1C = It.Row
End Function
--
Neal Z