MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Loop Help


Posted by Josh on December 22, 2001 1:58 PM

Hi

I am having trouble with a loop. I get an error "Object variable or with block not set"

Can someont tell me what I am doing wrong.

Thanks


Sub test()

Sheets("Post").Activate
lastrow = Range("A65536").End(xlUp).Row
Sheets("PTO").Activate
TotalReplace = 1

With Sheets("PTO").Range("H1:AB500")

Set c = .Find(what:="1", LookIn:=xlValues)

If Not c Is Nothing Then

lastaddress = c.Address

Do

replacevalue = Cells(c.Row, 1).Value
replacecolumn = c.Column - 7
counter = 2

Do
counter = counter + 1
Sheets("Post").Activate
Sheets(Range("A" & counter).Value).Activate
Columns(replacecolumn).Select
Selection.Replace what:=replacevalue, replacement:="open", lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
Loop While counter < lastrow

Set c = .FindNext(c)

Loop While Not c Is Nothing And c.Address <> lastaddress


Else
End If

End With

End Sub


Posted by Ivan F Moala on December 23, 2001 2:23 AM

Josh
Unsure with out testing BUT....this error
probably caused by your find routine NOT getting
a match.....try puting an Onerror resume next in
THEN handling the error with
If Err then do something else > skip ??


Ivan

If Not c Is Nothing Then lastaddress = c.Address Do replacevalue = Cells(c.Row, 1).Value replacecolumn = c.Column - 7 counter = 2 Do counter = counter + 1 Sheets("Post").Activate Sheets(Range("A" & counter).Value).Activate Columns(replacecolumn).Select Selection.Replace what:=replacevalue, replacement:="open", lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False Loop While counter < lastrow Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> lastaddress

Else End If End With End Sub