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

Nested with loops
 

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