| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find #Ref! and replace
How can i in code find alll cells that have #Ref! and replace it with 0?
-- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200707/1 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find #Ref! and replace
Assuming that would be the only error in the cells selected:
On Error Resume Next Selection.SpecialCells(xlFormulas,xlErrors) = 0 On Error goto 0 -- Regards, Tom Ogilvy "jln via OfficeKB.com" wrote: How can i in code find alll cells that have #Ref! and replace it with 0? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200707/1 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find #Ref! and replace
Tom like always it worked great.
Tom Ogilvy wrote: Assuming that would be the only error in the cells selected: On Error Resume Next Selection.SpecialCells(xlFormulas,xlErrors) = 0 On Error goto 0 How can i in code find alll cells that have #Ref! and replace it with 0? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200707/1 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find #Ref! and replace
How can i in code find alll cells that have #Ref! and replace it with 0?
Do this: Cells.Replace What:="#REF!", Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Brian Herbert Withun |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find #Ref! and replace
Just a heads up, but ift the #REF! is caused by a formula (and why else would
you have it), this method won't work. If you select the cells and do edit=copy, then Edit=Paste Special Values, then it will work. -- Regards, Tom Ogilvy "Brian" wrote: How can i in code find alll cells that have #Ref! and replace it with 0? Do this: Cells.Replace What:="#REF!", Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Brian Herbert Withun |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find #Ref! and replace
Another option based on Brian's suggestion:
Cells.Replace What:="*#REF!*", _ Replacement:="0", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False Brian wrote: How can i in code find alll cells that have #Ref! and replace it with 0? Do this: Cells.Replace What:="#REF!", Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Brian Herbert Withun -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find #Ref! and replace
?????
-- Regards, Tom Ogilvy "Dave Peterson" wrote: Another option based on Brian's suggestion: Cells.Replace What:="*#REF!*", _ Replacement:="0", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False Brian wrote: How can i in code find alll cells that have #Ref! and replace it with 0? Do this: Cells.Replace What:="#REF!", Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Brian Herbert Withun -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find #Ref! and replace
I surrounded the #REF! with wildcards: *#REF!*
and in my tests, the cells that contained any #REF! errors were changed to 0's. Or did you mean something else? Tom Ogilvy wrote: ????? -- Regards, Tom Ogilvy "Dave Peterson" wrote: Another option based on Brian's suggestion: Cells.Replace What:="*#REF!*", _ Replacement:="0", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False Brian wrote: How can i in code find alll cells that have #Ref! and replace it with 0? Do this: Cells.Replace What:="#REF!", Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Brian Herbert Withun -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find #Ref! and replace
I mean I ran your code and it did nothing for me in a worksheet where #REF!
is produced by a formula. Replace looks at the formula in the cell =someformula and #REF! will not be found there unless someone has something goofy like =if(True,"#REF!",";-)") xl2003 I ran your macro on my sheet and got #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! 0 0 0 0 0 #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! Guess which row I had done Edit=Copy, then Edit=Paste Special, then values. Maybe we are talking apples and oranges - enlighten me on "What I am missing" as you might say. Because of your great wisdom, I hesitate to question your suggestion, but in this case, I don't see it. -- Regards, Tom Ogilvy "Dave Peterson" wrote: I surrounded the #REF! with wildcards: *#REF!* and in my tests, the cells that contained any #REF! errors were changed to 0's. Or did you mean something else? Tom Ogilvy wrote: ????? -- Regards, Tom Ogilvy "Dave Peterson" wrote: Another option based on Brian's suggestion: Cells.Replace What:="*#REF!*", _ Replacement:="0", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False Brian wrote: How can i in code find alll cells that have #Ref! and replace it with 0? Do this: Cells.Replace What:="#REF!", Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Brian Herbert Withun -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
| Find & Replace and Find & Insert macro help needed | Excel Programming | |||
| find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
| Using Find and Replace to replace " in a macro | Excel Programming | |||
| Replace method - cannot find any data to replace | Excel Programming | |||
Hybrid Mode
