Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / March 2006

Tip: Looking for answers? Try searching our database.

Oject value changing in a loop

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Neal Zimm - 23 Mar 2006 13:31 GMT
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
Signature

Neal Z

Tom Ogilvy - 23 Mar 2006 13:46 GMT
'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

Signature

Regards,
Tom Ogilvy

> The proc below is a general search called by other procs.
> The IAllRtesOrOne input var determines if one or more sheets
[quoted text clipped - 96 lines]
> Next RteIx
> End Sub
Neal Zimm - 24 Mar 2006 09:05 GMT
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.
Signature

Neal Z

> 'Set RteCells = Worksheets(Route).Range(Cells(IFmRow, IFmCol),Cells(IToRow,
> IToCol))    
[quoted text clipped - 105 lines]
> > Next RteIx
> > End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.