Find and delete error value

  • Thread starter Thread starter KP
  • Start date Start date
K

KP

Hi,

I need a macro that can find the error value "#I/T" in the range A1:D10 and
clear the contents.
I tried this one below, but it didn't work, so I hope someone can correct it
or write a complete different macro doing the same thing.
Sub CheckCell()
Dim rCell As Range
Dim sMyString As String

Set rCell = Range("A1:D10")

If IsError(rCell.Value) Then

Cell.ClearContents

End If
End Sub

Regards,
Kaj Pedersen
 
Hi Kaj,

Am Sun, 2 Oct 2011 19:09:58 +0200 schrieb KP:
I need a macro that can find the error value "#I/T" in the range A1:D10 and
clear the contents.
Click to expand...

try:
Dim myRange As Range
Set myRange = Range("A1:D10")
myRange.SpecialCells(xlCellTypeFormulas, 16).ClearContents


Regards
Claus Busch
 
You didn't say if there was more than ONE. If only one just use FIND

Sub FindString()
On Error Resume Next 'GoTo 0
With Worksheets("sheet9").Range("a1:d50")
Set c = .Find(What:="#I/T", LookIn:=xlValues, _
lookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox c.address 'Row
c.Clear
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub
 
Hi Claus,

I have just tried your suggestion.
Unfortunately it resulted in Runtime Error 1004 (No cells were found)
Are you able to figure out what the problem is?

Kaj Pedersen
 
I am sorry about the insufficient information.
Actually there is more than one.
Do you have another suggestion?

Kaj Pedersen


"Don Guillett" <[email protected]> skrev i en meddelelse
You didn't say if there was more than ONE. If only one just use FIND

Sub FindString()
On Error Resume Next 'GoTo 0
With Worksheets("sheet9").Range("a1:d50")
Set c = .Find(What:="#I/T", LookIn:=xlValues, _
lookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox c.address 'Row
c.Clear
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub
 
Hi Kaj,

Am Sun, 2 Oct 2011 19:38:10 +0200 schrieb KP:
I have just tried your suggestion.
Unfortunately it resulted in Runtime Error 1004 (No cells were found)
Are you able to figure out what the problem is?
Click to expand...

if the error value is not a result of a formula, change the code from
xlCellTypeFormulas to xlCellTypeConstants:

myRange.SpecialCells(xlCellTypeConstants, 16).ClearContents


Regards
Claus Busch
 
Hi Claus,

I am very happy about your new suggestion.
Now it works as I want.
Your help is very much appreciated.

Best regards,
Kaj Pedersen
 
I am very happy about your new suggestion.
Now it works as I want.
Click to expand...

Really? It works? Exactly what is in your cells? You said your "error" was
#I/T... since #I/T is not a "real" Excel error, I don't see how Claus' code
could do what you want. Anyway, if your cells really have #I/T in them (as
constants, not formulas), then this code should replace each one of them
with the empty string, thus clearing those cells...

Sub ClearErrorIT()
Range("A1:D10").Replace "#I/T", "", xlWhole
End Sub

Rick Rothstein (MVP - Excel)
 
Hi Rick,

Thank you for taking part in my question. I have just tried your proposal:

Sub ClearErrorIT()
Range("A1:D10").Replace "#I/T", "", xlWhole
End Sub

It does not work. I receive no error message, but on the other hand nothing
happens when running the macro.
I continue to look up why Claus' works and not yours. I revert to the matter
tomorrow.

Kaj Pedersen
 
Rick Rothstein explained on 10/2/2011 :
Really? It works? Exactly what is in your cells? You said your "error" was
#I/T... since #I/T is not a "real" Excel error, I don't see how Claus' code
could do what you want. Anyway, if your cells really have #I/T in them (as
constants, not formulas), then this code should replace each one of them with
the empty string, thus clearing those cells...

Sub ClearErrorIT()
Range("A1:D10").Replace "#I/T", "", xlWhole
End Sub

Rick Rothstein (MVP - Excel)
Click to expand...

Rick, I don't see the trailing "!" normally associated with errors in
Kaj's post. Perhaps change the last arg to 'xlPart'??
 
Rick, I don't see the trailing "!" normally associated with
errors in Kaj's post. Perhaps change the last arg to 'xlPart'??
Click to expand...

That wouldn't help as the Replace function would leave the exclamation mark
after the replacement. If what Kaj posted is not what he actually has in the
cell, he needs to tell us so we can adjust our code to account for it. I'm
still puzzled as to how Claus' code is working for Kaj.

Rick Rothstein (MVP - Excel)
 
Really? It works? Exactly what is in your cells? You said your "error" was
#I/T... since #I/T is not a "real" Excel error, I don't see how Claus' code
could do what you want. Anyway, if your cells really have #I/T in them (as
constants, not formulas), then this code should replace each one of them
with the empty string, thus clearing those cells...
Click to expand...

Seems that #I/T is a real error (just not in the English version
maybe...)

http://office.microsoft.com/da-dk/excel-help/rette-fejlen-i-t-HP010342325.aspx
 
Hi Rick,

Yes, I can confirm that the local version seems to be the reason.
I was running the file on a danish version of Excel 2003.
Today I had the possibility to run the file on an English version of Excel
2007.
In the Danish version, #I/T was shown, but in the Enhlish version it was
#N/A

Kaj Pedersen
 
Back
Top