Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object syntax with a Period ( . )
I took the code below from the VBA help example for the
Find Next method, and am in process of learning more about it and building a general find proc that can be called. In this line, Set InfoCell = .Find(IFindThis, LookIn:=xlFormulas) I remember reading somewhere about the period(.) being placed before the word Find. Not that I'm looking for that documentation, I can't find it. (no pun intended) Can someone point me to the right section and/or give me a brief explanation of the .Find syntax? thanks, Neal Z Sub zzz_Find_Method(IFindThis) 'This example finds all cells in the range A1:A25 that _ contain the value in the IFindThis var. Dim InfoCell As Object Dim FirstAddress 'With Worksheets("2222-0900").Range("a1:a25") With Worksheets("2222-0900").Range(Cells(1, 1), Cells(25, 1)) Set InfoCell = .Find(IFindThis, LookIn:=xlFormulas) If Not InfoCell Is Nothing Then FirstAddress = InfoCell.Address MsgBox "FirstAddress= " & FirstAddress & Cr _ & "InfoCell.Row= " & InfoCell.Row & " InfoCell.Column= " & InfoCell.Column Do Set InfoCell = .FindNext(InfoCell) If Not InfoCell Is Nothing And InfoCell.Address < FirstAddress _ Then MsgBox InfoCell.Address Loop Until Not InfoCell Is Nothing And InfoCell.Address = FirstAddress Else MsgBox IFindThis & " is NOT found." End If End With End Sub -- Neal Z |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object syntax with a Period ( . )
You're looking for the With statement. This allows you to
reference an object one in the With statement, and the access its properties and methods using just a period. E.g., With Range("A1:A10" Set InfoCell = .Find(IFindThis, lookin:=xlFormulas) End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Neal Zimm" wrote in message ... I took the code below from the VBA help example for the Find Next method, and am in process of learning more about it and building a general find proc that can be called. In this line, Set InfoCell = .Find(IFindThis, LookIn:=xlFormulas) I remember reading somewhere about the period(.) being placed before the word Find. Not that I'm looking for that documentation, I can't find it. (no pun intended) Can someone point me to the right section and/or give me a brief explanation of the .Find syntax? thanks, Neal Z Sub zzz_Find_Method(IFindThis) 'This example finds all cells in the range A1:A25 that _ contain the value in the IFindThis var. Dim InfoCell As Object Dim FirstAddress 'With Worksheets("2222-0900").Range("a1:a25") With Worksheets("2222-0900").Range(Cells(1, 1), Cells(25, 1)) Set InfoCell = .Find(IFindThis, LookIn:=xlFormulas) If Not InfoCell Is Nothing Then FirstAddress = InfoCell.Address MsgBox "FirstAddress= " & FirstAddress & Cr _ & "InfoCell.Row= " & InfoCell.Row & " InfoCell.Column= " & InfoCell.Column Do Set InfoCell = .FindNext(InfoCell) If Not InfoCell Is Nothing And InfoCell.Address < FirstAddress _ Then MsgBox InfoCell.Address Loop Until Not InfoCell Is Nothing And InfoCell.Address = FirstAddress Else MsgBox IFindThis & " is NOT found." End If End With End Sub -- Neal Z |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object syntax with a Period ( . )
workbooks("a.xls").Worksheets(1).Range("A1:Z26").F ind
What:=workbooks("a.xls").Worksheets(1).Range("M1") , After:=workbooks("a.xls").Worksheets(1).Range("Z26 ") It a complete qualification of a range used to be searched by the find command. For convenience you can use the with statement workbooks("a.xls").Worksheets(1) ..Range("A1:Z26").Find what:=.Range("M1"), _ After:=Range("Z26") End With each reference that has a leading period is referring back to the with statement. There is nothing unique to the Find command and using a leading period. -- Regards, Tom Ogilvy "Neal Zimm" wrote: I took the code below from the VBA help example for the Find Next method, and am in process of learning more about it and building a general find proc that can be called. In this line, Set InfoCell = .Find(IFindThis, LookIn:=xlFormulas) I remember reading somewhere about the period(.) being placed before the word Find. Not that I'm looking for that documentation, I can't find it. (no pun intended) Can someone point me to the right section and/or give me a brief explanation of the .Find syntax? thanks, Neal Z Sub zzz_Find_Method(IFindThis) 'This example finds all cells in the range A1:A25 that _ contain the value in the IFindThis var. Dim InfoCell As Object Dim FirstAddress 'With Worksheets("2222-0900").Range("a1:a25") With Worksheets("2222-0900").Range(Cells(1, 1), Cells(25, 1)) Set InfoCell = .Find(IFindThis, LookIn:=xlFormulas) If Not InfoCell Is Nothing Then FirstAddress = InfoCell.Address MsgBox "FirstAddress= " & FirstAddress & Cr _ & "InfoCell.Row= " & InfoCell.Row & " InfoCell.Column= " & InfoCell.Column Do Set InfoCell = .FindNext(InfoCell) If Not InfoCell Is Nothing And InfoCell.Address < FirstAddress _ Then MsgBox InfoCell.Address Loop Until Not InfoCell Is Nothing And InfoCell.Address = FirstAddress Else MsgBox IFindThis & " is NOT found." End If End With End Sub -- Neal Z |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object syntax with a Period ( . )
Once again, thanks much.
-- Neal Z "Chip Pearson" wrote: You're looking for the With statement. This allows you to reference an object one in the With statement, and the access its properties and methods using just a period. E.g., With Range("A1:A10" Set InfoCell = .Find(IFindThis, lookin:=xlFormulas) End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Neal Zimm" wrote in message ... I took the code below from the VBA help example for the Find Next method, and am in process of learning more about it and building a general find proc that can be called. In this line, Set InfoCell = .Find(IFindThis, LookIn:=xlFormulas) I remember reading somewhere about the period(.) being placed before the word Find. Not that I'm looking for that documentation, I can't find it. (no pun intended) Can someone point me to the right section and/or give me a brief explanation of the .Find syntax? thanks, Neal Z Sub zzz_Find_Method(IFindThis) 'This example finds all cells in the range A1:A25 that _ contain the value in the IFindThis var. Dim InfoCell As Object Dim FirstAddress 'With Worksheets("2222-0900").Range("a1:a25") With Worksheets("2222-0900").Range(Cells(1, 1), Cells(25, 1)) Set InfoCell = .Find(IFindThis, LookIn:=xlFormulas) If Not InfoCell Is Nothing Then FirstAddress = InfoCell.Address MsgBox "FirstAddress= " & FirstAddress & Cr _ & "InfoCell.Row= " & InfoCell.Row & " InfoCell.Column= " & InfoCell.Column Do Set InfoCell = .FindNext(InfoCell) If Not InfoCell Is Nothing And InfoCell.Address < FirstAddress _ Then MsgBox InfoCell.Address Loop Until Not InfoCell Is Nothing And InfoCell.Address = FirstAddress Else MsgBox IFindThis & " is NOT found." End If End With End Sub -- Neal Z |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object syntax with a Period ( . )
Once again, thanks much.
-- Neal Z "Tom Ogilvy" wrote: workbooks("a.xls").Worksheets(1).Range("A1:Z26").F ind What:=workbooks("a.xls").Worksheets(1).Range("M1") , After:=workbooks("a.xls").Worksheets(1).Range("Z26 ") It a complete qualification of a range used to be searched by the find command. For convenience you can use the with statement workbooks("a.xls").Worksheets(1) .Range("A1:Z26").Find what:=.Range("M1"), _ After:=Range("Z26") End With each reference that has a leading period is referring back to the with statement. There is nothing unique to the Find command and using a leading period. -- Regards, Tom Ogilvy "Neal Zimm" wrote: I took the code below from the VBA help example for the Find Next method, and am in process of learning more about it and building a general find proc that can be called. In this line, Set InfoCell = .Find(IFindThis, LookIn:=xlFormulas) I remember reading somewhere about the period(.) being placed before the word Find. Not that I'm looking for that documentation, I can't find it. (no pun intended) Can someone point me to the right section and/or give me a brief explanation of the .Find syntax? thanks, Neal Z Sub zzz_Find_Method(IFindThis) 'This example finds all cells in the range A1:A25 that _ contain the value in the IFindThis var. Dim InfoCell As Object Dim FirstAddress 'With Worksheets("2222-0900").Range("a1:a25") With Worksheets("2222-0900").Range(Cells(1, 1), Cells(25, 1)) Set InfoCell = .Find(IFindThis, LookIn:=xlFormulas) If Not InfoCell Is Nothing Then FirstAddress = InfoCell.Address MsgBox "FirstAddress= " & FirstAddress & Cr _ & "InfoCell.Row= " & InfoCell.Row & " InfoCell.Column= " & InfoCell.Column Do Set InfoCell = .FindNext(InfoCell) If Not InfoCell Is Nothing And InfoCell.Address < FirstAddress _ Then MsgBox InfoCell.Address Loop Until Not InfoCell Is Nothing And InfoCell.Address = FirstAddress Else MsgBox IFindThis & " is NOT found." End If End With End Sub -- Neal Z |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object syntax, multiple worksheets
Hi again,
The proc below works, but not quite in the way I had anticipated. The object in line B below did not work when searching more than 1 worksheet and I don't know enough yet to understand why. My work around was to use lines D and E. Can you explain the problem? Thanks again, Neal Z. Sub zString_FindGeneral(ByRef IFindThis As String, _ ByRef IAllRtesOrOne As String, _ ByRef IFmRow As Long, ByRef IFmCol As Integer, ByRef IToRow As Long, ByRef IToCol As Integer, _ ByRef OErrMsg As String, _ ByRef OFoundQty As Integer, ByRef OFoundAry As Variant) ' Fill the OFoundAry array with the results of a string search; ' Calling macro determines if one sheet or more will be searched. ' Array column 1= worksheet name, 2= row number, 3= column number ' OFoundQty is the number of cells found containing the IFindThis string. Dim RteQty As Integer, Route As String Dim RteIx As Integer ' index Dim RteNameAry(1 To 26) As String Dim RteCells As Object, FirstAddress As String, FoundCell As Object Const AryRteCol = 1 ' Columns in the OFoundAry Const AryRowCol = 2 Const AryColCol = 3 OFoundQty = 0 OErrMsg = "" Application.ScreenUpdating = False If LCase(IAllRtesOrOne) = "all" Then 'makes an array of selected worksheet names Call zRte_NameAry_Make(Yes, No, "", RteQty, RteNameAry, OErrMsg) If OErrMsg < "" Then Exit Sub Else RteQty = 1 RteNameAry(1) = ActiveSheet.name End If For RteIx = 1 To RteQty 'LINES A, B, C WORK WHEN SEARCHING ONE WORKSHEET (RteQty=1), D E were commented out. 'line B errors out with application or object defined error when RteQty is 1, i.e. more than 1 sheet Route = RteNameAry(RteIx) 'LINE A 'Set RteCells = Worksheets(Route).Range(Cells(IFmRow, IFmCol), Cells(IToRow, IToCol)) 'LINE B 'With RteCells 'LINE C 'Lines A, D, E work when searching one or multiple worksheets. B C were commented out. Worksheets(Route).Activate 'LINE D With Range(Cells(IFmRow, IFmCol), Cells(IToRow, IToCol)) 'LINE E Set FoundCell = .Find(IFindThis, LookIn:=xlFormulas) If Not FoundCell Is Nothing Then FirstAddress = FoundCell.Address OFoundQty = OFoundQty + 1 OFoundAry(OFoundQty, AryRteCol) = Route OFoundAry(OFoundQty, AryRowCol) = FoundCell.Row OFoundAry(OFoundQty, AryColCol) = FoundCell.Column Do Set FoundCell = .FindNext(FoundCell) If Not FoundCell Is Nothing And FoundCell.Address < FirstAddress Then If OFoundQty < UBound(OFoundAry, 1) Then OFoundQty = OFoundQty + 1 OFoundAry(OFoundQty, AryRteCol) = Route OFoundAry(OFoundQty, AryRowCol) = FoundCell.Row OFoundAry(OFoundQty, AryColCol) = FoundCell.Column Else MsgBox "Program limit of " & UBound(OFoundAry, 1) & " found cells has been reached." Exit Do ' todo, expand above error message pgm limit reached End If End If Loop Until Not FoundCell Is Nothing And FoundCell.Address = FirstAddress Else ' IFindThis string is not found the first time End If End With If OFoundQty = UBound(OFoundAry, 1) Then Exit For Next RteIx End Sub -- Neal Z |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object syntax with multiple worksheets
Hi again,
The proc below works, but not quite in the way I had anticipated. The object in line B below did not work when searching more than 1 worksheet and I don't know enough yet to understand why. My work around was to use lines D and E. Can you explain the problem? Thanks again, Neal Z. Sub zString_FindGeneral(ByRef IFindThis As String, _ ByRef IAllRtesOrOne As String, _ ByRef IFmRow As Long, ByRef IFmCol As Integer, ByRef IToRow As Long, ByRef IToCol As Integer, _ ByRef OErrMsg As String, _ ByRef OFoundQty As Integer, ByRef OFoundAry As Variant) ' Fill the OFoundAry array with the results of a string search; ' Calling macro determines if one sheet or more will be searched. ' Array column 1= worksheet name, 2= row number, 3= column number ' OFoundQty is the number of cells found containing the IFindThis string. Dim RteQty As Integer, Route As String Dim RteIx As Integer ' index Dim RteNameAry(1 To 26) As String Dim RteCells As Object, FirstAddress As String, FoundCell As Object Const AryRteCol = 1 ' Columns in the OFoundAry Const AryRowCol = 2 Const AryColCol = 3 OFoundQty = 0 OErrMsg = "" Application.ScreenUpdating = False If LCase(IAllRtesOrOne) = "all" Then 'makes an array of selected worksheet names Call zRte_NameAry_Make(Yes, No, "", RteQty, RteNameAry, OErrMsg) If OErrMsg < "" Then Exit Sub Else RteQty = 1 RteNameAry(1) = ActiveSheet.name End If For RteIx = 1 To RteQty 'LINES A, B, C WORK WHEN SEARCHING ONE WORKSHEET (RteQty=1), D E were commented out. 'line B errors out with application or object defined error when RteQty is 1, i.e. more than 1 sheet Route = RteNameAry(RteIx) 'LINE A 'Set RteCells = Worksheets(Route).Range(Cells(IFmRow, IFmCol), Cells(IToRow, IToCol)) 'LINE B 'With RteCells 'LINE C 'Lines A, D, E work when searching one or multiple worksheets. B C were commented out. Worksheets(Route).Activate 'LINE D With Range(Cells(IFmRow, IFmCol), Cells(IToRow, IToCol)) 'LINE E Set FoundCell = .Find(IFindThis, LookIn:=xlFormulas) If Not FoundCell Is Nothing Then FirstAddress = FoundCell.Address OFoundQty = OFoundQty + 1 OFoundAry(OFoundQty, AryRteCol) = Route OFoundAry(OFoundQty, AryRowCol) = FoundCell.Row OFoundAry(OFoundQty, AryColCol) = FoundCell.Column Do Set FoundCell = .FindNext(FoundCell) If Not FoundCell Is Nothing And FoundCell.Address < FirstAddress Then If OFoundQty < UBound(OFoundAry, 1) Then OFoundQty = OFoundQty + 1 OFoundAry(OFoundQty, AryRteCol) = Route OFoundAry(OFoundQty, AryRowCol) = FoundCell.Row OFoundAry(OFoundQty, AryColCol) = FoundCell.Column Else MsgBox "Program limit of " & UBound(OFoundAry, 1) & " found cells has been reached." Exit Do ' todo, expand above error message pgm limit reached End If End If Loop Until Not FoundCell Is Nothing And FoundCell.Address = FirstAddress Else ' IFindThis string is not found the first time End If End With If OFoundQty = UBound(OFoundAry, 1) Then Exit For Next RteIx End Sub -- Neal Z -- Neal Z |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vba syntax for excel object | Excel Discussion (Misc queries) | |||
Syntax in Varying Object Names | Excel Programming | |||
Formula syntax, "Object-defined error" | Excel Programming | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming | |||
Proper syntax to Set a Workbook Object? | Excel Programming |