Run-Time error '1004': No cells were found

[Guest]

The subject line is the error message I get when trying to run the following
code. When I hit debug, it highlights the line that starts with "set r -
range...". What I am trying to do is clear the constants but leave the
formulas in the "D7:E125" Range. Help!!!!

' Sub clear_part2()
Dim r As Range
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
r.Clear

End Sub

 

[Guest]

Regina,
Try this

Sub clear_part2()
Dim r As Range
On Error Resume Next
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not r Is Nothing Then
r.Clear
End If
End Sub

 

[Guest]

Hi Alok,
I copied your reply directly into the macro and it didn't do any actions.
It didn't error but there wasn't any action either. Thanks for you help!
Any other suggestions?

 

[Bob Phillips]

Have you got any cells with constant values? Alok's code tested the range
first for matches, and does nothing if there are none.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Regina,
The best method to see why nothing is happening is to debug the code by
walking through it.
Click on the line Set r = ..
Press F9
This will set a break point on the line.
Run the macro by pressing F5.
The yellow line will appear and will overlap the break point.
Now step through the code by pressing F8
If your code skips over the line
r.Clear
then it means the code did not find any cells containing constants.
If it goes into that line (that is the line r.Clear becomes yellow) then you
can check the address of the cells which the code found to be the cells
containing the constants by typeing ? r.address in the immediate window. (If
the immediate window is not in view type Control-G to view it)

Hope this helps.

 

[Guest]

Regina,

Also please note that your code currently works on the active sheet.

 

[Guest]

Maybe you want to use just xlConstants instead of xlCellTypeConstants.

 

[Guest]

Hi JLGWhiz,
There is no Enumerated value of xlConstants in the XlCellType enumeration.
Hence that is unlikely to be the problem.
Alok

 

[Dave Peterson]

From the immediate window in xl2003's VBE:

?xlConstants
2

?xlcelltypeConstants
2

But I don't think that's the problem, either.

 

[Guest]

I agree with you Dave that in this case both constants happen to have the
same value but using the other defeats the purpose of using incorrect
constant defeats the purpose and makes the code even more difficult to
maintain than using say the value of 2.

 

[Dave Peterson]

I think xlconstants was the original constant. xlcelltypeconstants came along
in xl97 (IIRC). But for compatibility, MS had to keep the old one alive. So I
don't think I'd characterize using xlconstants as being incorrect (although I
favor the newer versions, too).

I agree with you Dave that in this case both constants happen to have the
same value but using the other defeats the purpose of using incorrect
constant defeats the purpose and makes the code even more difficult to
maintain than using say the value of 2.

 

[Guest]

I'm sorry guys, I have been in meetings since yesterday afternoon. I wasn't
ignoring. I will try these suggestions and let you know.

Thanks,

 

[Guest]

None of this is working. I am not very good in VB so please bare with me. I
think some of my terminology was wrong in the first question that I asked,
therefore I am sure that is why this is not doing what I want. I said I
wanted to clear the constants and leave the formulas. What I am trying to do
is clear data that is in a linked cell from a worksheet within the same
workbook and retain the links. Thanks,

 

[Bob Phillips]

You can't, you have to clear the data in the source cell(s).

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)