Do Loop error

[schorley]

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]

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
--
Regards,

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