Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find and With stmts
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find and With stmts
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find and With stmts
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find and With stmts
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 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 -- Neal Z "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple If Stmts | Excel Discussion (Misc queries) | |||
Nested 'If" stmts | Excel Discussion (Misc queries) | |||
Creating macros with if stmts | Excel Discussion (Misc queries) | |||
Monthly bank stmts | Excel Worksheet Functions | |||
simplify SUMIF stmts | Excel Programming |