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