Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error dialog box meaning and how to find error | Excel Worksheet Functions | |||
Find and Replace - delete the remainder of the text in the cell after my Find | Excel Programming | |||
help with this error-Compile error: cant find project or library | Excel Discussion (Misc queries) | |||
find and delete duplicate entries in two columns or find and prin. | Excel Programming | |||
find and delete text, find a 10-digit number and put it in a textbox | Excel Programming |