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 / New Users / October 2006

Tip: Looking for answers? Try searching our database.

Do Loop error

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
schorley@gmail.com - 17 Oct 2006 11:50 GMT
Hi

I keep getting error 91 messages (Object variable or with block
variable not set) with the below code. It happens when FoundRange comes
back as Nothing in the loop; but I'm not sure why it should come back
as Nothing. Surely it should just go back to firstFind, then the loop
should be exited?!

Thanks!

   Set FoundRange = Nothing
   Set FoundRange = SearchRange.Find("3600")
   If Not (FoundRange Is Nothing) Then
       firstFind = FoundRange.Address
       Do
           If Left(FoundRange.Formula, 1) = "=" Then
               FoundRange.Replace What:="3600", Replacement:="GWhToGJ"
           End If
           Set FoundRange = SearchRange.FindNext(FoundRange)
       Loop Until (FoundRange Is Nothing) Or (FoundRange.Address =
firstFind)
   End If
Tushar Mehta - 17 Oct 2006 12:14 GMT
Why would you expect FoundRange to "re-find" firstFind?  Haven't you
replaced the 3600 with something else?

The problem is compounded by how VB(A) examines compound boolean
expressions.  It insists on evaluating all elements even if the final
decision can be reached with only a partial examination.  So, in the Loop
Until x OR y construct, VB(A) will try and evaluate both X and Y.  
Unfortunately, if X is true, Y will fail!

You may have to develop a somewhat more cumbersome mechanism to exit the
loop.  Or, use
FindAll
http://www.tushar-mehta.com/excel/tips/findall.html

If you still want to roll your own, use something along the lines of the
untested

   Set FoundRange = Nothing
   Set FoundRange = SearchRange.Find("3600")
   If Not (FoundRange Is Nothing) Then
       firstFind = FoundRange.Address

       Dim AllDone as boolean 'new/changed
       AllDone=false '

       Do
           If Left(FoundRange.Formula, 1) = "=" Then
               FoundRange.Replace What:="3600", Replacement:="GWhToGJ"
           End If
           Set FoundRange = SearchRange.FindNext(FoundRange)

           AllDone=FoundRange is nothing '
           if not alldone then alldone=foundrange.address=firstFind '
       Loop Until AllDone '
   End If
Signature

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

> Hi
>
[quoted text clipped - 18 lines]
> firstFind)
>     End If
 
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.