Find and delete error value
 

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

Find and delete error value
 

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.

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


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Find and delete error value
 

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




On Oct 2, 12:09*pm, "KP" <none wrote:
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

Find and delete error value
 

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


"Claus Busch" skrev i en meddelelse
...
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.

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


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Find and delete error value
 

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

Kaj Pedersen


"Don Guillett" 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




On Oct 2, 12:09 pm, "KP" <none wrote:
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

Find and delete error value
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Find and delete error value
 

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



"Claus Busch" skrev i en meddelelse
...
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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Find and delete error value
 

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

Rick Rothstein (MVP - Excel)

Find and delete error value
 

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" skrev i en meddelelse
...
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("A1:D10").Replace "#I/T", "", xlWhole
End Sub

Rick Rothstein (MVP - Excel)

Find and delete error value
 

Rick Rothstein explained on 10/2/2011 :
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("A1:D10").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'??

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Find and delete error value
 

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)

Find and delete error value
 

On Oct 2, 12:05*pm, "Rick Rothstein"
wrote:
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...

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

http://office.microsoft.com/da-dk/ex...010342325.aspx

Find and delete error value
 

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

http://office.microsoft.com/da-dk/ex...010342325.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)

Find and delete error value
 

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

"Rick Rothstein" skrev i en meddelelse
...
Seems that #I/T is a real error (just not in the English
version maybe...)

http://office.microsoft.com/da-dk/ex...010342325.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)