.Find and With stmts

[Neal Zimm]

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

 

[Jim Thomlinson]

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)

 

[Jim Thomlinson]

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.

 

[Neal Zimm]

Jim - Thanks, I want be sure on the method going forward. I did know that my
'B' example, repeated below, did not contain the dots, but since the cells
were IN the with statement, I did not think I needed them.

With Ws.Range(Cells(FmRow, Col), Cells(ToRow, Col)) 'still bad
Set It = .Find(sLookFor, LookIn:=xlValues, LookAt:=WhoOrPrt)
End With

as corrected: I will test this soon, but it still "looks funny"
With Ws.Range(.Cells(FmRow, Col), .Cells(ToRow, Col))
Set It = .Find(sLookFor, LookIn:=xlValues, LookAt:=WhoOrPrt)
End With

re: your efficiency comment, since this is a 'one-time' search, I guess I
could string the whole thing out, not using the 'with' construct at all, e.g.

Set It = Ws.Range(.Cells(FmRow, Col),.Cells(ToRow, Col)).Find(sLookFor, _
LookIn:=xlValues, LookAt:=WhoOrPrt) 'Yes ??

I would NOT need the dots for something like this:
Set It = workbooks("name").sheets("name2").Range(cells(x,y), etc......

Thanks, to summarize? If you use Ws. as an object, ya' need da dots.
Neal