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.

Nested with loops

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Clair - 20 Mar 2006 18:48 GMT
Hi,

Not sure if anyone can explain why I'm getting a run time error '91'
(object variable or with block variable not set) when I try to run this
nested loop - I'm out of thoughts in trying to troubleshoot it.  Any
thoughts would be greatly appreciated!

What I'm trying to - I have a large sheet of data and I'll need to
correct some formulas that occur in multiple columns and rows.  So I
was just trying to loop through the appropriate columns and rows by
finding the appropriate ones, and correct each formula cell
individually.

Thanks!  Clair.

   With ActiveSheet.Columns(2)
       Set c = .Find(What:=" Total", LookIn:=xlValues, LookAt:=xlPart)
       If Not c Is Nothing Then
           firstaddress = c.Address
           Do
               c.Select
               With ActiveSheet.Rows("3:3")
               Set d = .Find(What:="% Utilization", LookIn:=xlValues,
LookAt:=xlPart)
                   If Not d Is Nothing Then
                       dfirstaddress = d.Address
                       Do
                           d.Select
                           ' loop actions here
                           Set d = .FindNext(d)
                       Loop While Not d Is Nothing And d.Address <>
dfirstaddress
                   End If
               End With
               Set c = .FindNext(c)
           Loop While Not c Is Nothing And c.Address <> firstaddress
       End If
   End With
Tom Ogilvy - 20 Mar 2006 20:29 GMT
you can't findnext on the first find after you have done an subsequent find.  
However, you don't have to.  Try it this way:  

Sub aaa()
Dim c As Range, d As Range
Dim dfirstaddress As String
Dim firstaddress As String
With ActiveSheet.Columns(2)
Set c = .Find(What:=" Total", _
 After:=Range("B65536"), LookIn:=xlValues, LookAt:=xlPart)
If Not c Is Nothing Then
 firstaddress = c.Address
 Do
  c.Select
  Selection.Interior.ColorIndex = 3
  With ActiveSheet.Rows("3:3")
   Set d = .Find(What:="% Utilization", LookIn:=xlValues, LookAt:=xlPart)
    If Not d Is Nothing Then
     dfirstaddress = d.Address
     Do
      d.Select
       ' loop actions here
      Set d = .FindNext(d)
     Loop While Not d Is Nothing And d.Address <> dfirstaddress
    End If
   End With
   Set c = .Find(What:=" Total", _
     After:=c, LookIn:=xlValues, LookAt:=xlPart)
 Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
End Sub

Signature

Regards,
Tom Ogilvy

> Hi,
>
[quoted text clipped - 34 lines]
>         End If
>     End With
 
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.