Find and delete error value

[KP]

Hi,

I need a macro that can find the error value "#I/T" in the range A110 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("A110")

If IsError(rCell.Value) Then

Cell.ClearContents

End If
End Sub

Regards,
Kaj Pedersen

 

[Claus Busch]

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 A110 and
clear the contents.

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


Regards
Claus Busch

 

[Don Guillett]

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

 

[KP]

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

 

[KP]

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

 

[Claus Busch]

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?

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

 

[KP]

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

 

[Rick Rothstein]

I am very happy about your new suggestion.

Now it works as I want.

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("A110").Replace "#I/T", "", xlWhole
End Sub

Rick Rothstein (MVP - Excel)

 

[KP]

Hi Rick,

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

Sub ClearErrorIT()
Range("A110").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

 

[GS]

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("A110").Replace "#I/T", "", xlWhole
End Sub

Rick Rothstein (MVP - Excel)

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

 

[Rick Rothstein]

Rick, I don't see the trailing "!" normally associated with

errors in Kaj's post. Perhaps change the last arg to 'xlPart'??

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)

 

[Tim Williams]

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

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

 

[Rick Rothstein]

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

Ah, yes, that makes sense. The OP's good English fooled me into thinking
that was a locally made up error message. Thanks for clearing that up for
me.

Rick Rothstein (MVP - Excel)

 

[KP]

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