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

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

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

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

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

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

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