Problem with Loop

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi. The following Loop works well until all of the items
have been replaced. In the final loop, I receive the
following error: "Object variable or With Block Variable
not set."

Any ideas?

Thanks,
Mike.
--------
With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <>
firstAddress
End If
End With
 
Mike,

The problem is that if c is nothing, c.Address will fail with an
error 91. Try something like the following:

Dim C As Range
Dim FirstAddress As String
Dim Done As Boolean

With Worksheets(1).Range("a1:a500")
Set C = .Find(2, LookIn:=xlValues)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
C.Value = 5
Set C = .FindNext(C)

If C Is Nothing Then
Done = True
Else
If C.Address = FirstAddress Then
Done = True
End If
End If

Loop While Done = False
End If
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Hi Mike,

Odd isn't it. This is straight from help and it doesn't work

The reason that it does not work is because when it is not found, c is
nothing, and thus it is impossible to get the address of c.

AFAICS all you need is

Loop While Not c Is Nothing

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Chip:

That works great! Could you tell me if there would be a
way to also clear the contents of three cells to the
right of where the value was found?

Thanks again,
Mike.
 
Bob:

Thanks!

It is odd ... It's neat that you recognized this from
HELP.

Thanks again,
Mike.
 
c.Offset(0,1).resize(1,3).clearcontents

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I think I have looked up this help topic before and remembered it. Although
I never use it in total as you did.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks again, Bob!

Mike.
-----Original Message-----

c.Offset(0,1).resize(1,3).clearcontents

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.
Click to expand...
 
Back
Top