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.

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

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

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

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

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)

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

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