Problem with Loop

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
 
C

Chip Pearson

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
 
B

Bob Phillips

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

Mike

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

Mike

Bob:

Thanks!

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

Thanks again,
Mike.
 
B

Bob Phillips

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

Bob Phillips

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

Mike

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