Oject value changing in a loop

[Neal Zimm]

The proc below is a general search called by other procs.
The IAllRtesOrOne input var determines if one or more sheets
will potentially be searched.

At issue are code lines A B C or A D E as described below,
especially line B.
I am still learning about objects, but ADE works as planned
with one or more than one sheets being searched.

ABC works when only one sheet is searched, but line B gets a
run time object error when looping to the second sheet.

I don't understand the problem. Help please.

Thanks,
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 two dimension OFoundAry
Const AryRowCol = 2
Const AryColCol = 3

OFoundQty = 0
OErrMsg = ""

Application.ScreenUpdating = False
If LCase(IAllRtesOrOne) = "all" Then
'make array of selected sheet names
Call zRte_NameAry_Make(Yes, No, "", RteQty, RteNameAry, OErrMsg)
If OErrMsg <> "" Then Exit Sub
Else
RteQty = 1
RteNameAry(1) = activesheet.name
End If

Set FoundCell = .Find(IFindThis, LookIn:=xlFormulas)

For RteIx = 1 To RteQty

'LINES A B C WORK WHEN SEARCHING ONE WORKSHEET (RteQty=1),
' lines 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 is supposed to be searched.

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
' with lines B C commented out.

Worksheets(Route).Activate 'LINE D
With Range(Cells(IFmRow, IFmCol), Cells(IToRow, IToCol)) 'LINE E
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
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

 

[Tom Ogilvy]

'Set RteCells = Worksheets(Route).Range(Cells(IFmRow, IFmCol),Cells(IToRow,
IToCol))

should be
With Worksheets(Route)
set RteCells = .Range(.Cells(IFmRow,IFmCol), _
.Cells(IToRow,IToCol))
End With

 

[Neal Zimm]

Tom -
Aha! The fog lifts....
So when you're within a 'With' construct, the item having the suffix to the
"with suffix" phrase must be proceeded by the period.
thanks much.