Nested with loops

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

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

--
Regards,
Tom Ogilvy

"Clair" wrote:

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