Problem with Loop
 

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

Problem with Loop
 

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



"Mike" wrote in message
...
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

Problem with Loop
 

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)

"Mike" wrote in message
...
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

Problem with Loop
 

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.


-----Original Message-----
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



"Mike" wrote in
message
...
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



.

Problem with Loop
 

Bob:

Thanks!

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

Thanks again,
Mike.


-----Original Message-----
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)

"Mike" wrote in
message
...
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



.

Problem with Loop
 

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)

"Mike" wrote in message
...
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.


-----Original Message-----
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



"Mike" wrote in
message
...
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



.

Problem with Loop
 

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)

"Mike" wrote in message
...
Bob:

Thanks!

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

Thanks again,
Mike.


-----Original Message-----
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)

"Mike" wrote in
message
...
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



.

Problem with Loop
 

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)

"Mike" wrote in
message
...
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.


-----Original Message-----
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



"Mike" wrote in
message
...
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



.



.