|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Find #Ref! and replace
I put a bunch of formulas starting in D6:
=A1+B1+C1 =B1+C1+D1 =C1+D1+E1 =A2+B2+C2 =B2+C2+D2 =C2+D2+E2 =A3+B3+C3 =B3+C3+D3 =C3+D3+E3 =A4+B4+C4 =B4+C4+D4 =C4+D4+E4 =A5+B5+C5 =B5+C5+D5 =C5+D5+E5 Then I deleted column A and row 1: My formulas changed to: =#REF!+#REF!+#REF! =#REF!+#REF!+#REF! =#REF!+#REF!+#REF! =#REF!+A1+B1 =A1+B1+C1 =B1+C1+D1 =#REF!+A2+B2 =A2+B2+C2 =B2+C2+D2 =#REF!+A3+B3 =A3+B3+C3 =B3+C3+D3 =#REF!+A4+B4 =A4+B4+C4 =B4+C4+D4 =#REF!+A5+B5 =A5+B5+C5 =B5+C5+D5 And then ran that line of code. My formulas that were broken were changed to 0's. I also used xl2003. And I tested a few times before my original post--and a couple times with this one. Maybe it was a differences in the type of broken formulas we used??? And when it comes down to wisdom, I'll defer to yours <bg. Tom Ogilvy wrote: 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 -- Dave Peterson |
Find #Ref! and replace
On Jul 13, 12:20 pm, Dave Peterson wrote:
I put a bunch of formulas starting in D6: =A1+B1+C1 =B1+C1+D1 =C1+D1+E1 =A2+B2+C2 =B2+C2+D2 =C2+D2+E2 =A3+B3+C3 =B3+C3+D3 =C3+D3+E3 =A4+B4+C4 =B4+C4+D4 =C4+D4+E4 =A5+B5+C5 =B5+C5+D5 =C5+D5+E5 Then I deleted column A and row 1: My formulas changed to: =#REF!+#REF!+#REF! =#REF!+#REF!+#REF! =#REF!+#REF!+#REF! =#REF!+A1+B1 =A1+B1+C1 =B1+C1+D1 =#REF!+A2+B2 =A2+B2+C2 =B2+C2+D2 =#REF!+A3+B3 =A3+B3+C3 =B3+C3+D3 =#REF!+A4+B4 =A4+B4+C4 =B4+C4+D4 =#REF!+A5+B5 =A5+B5+C5 =B5+C5+D5 And then ran that line of code. My formulas that were broken were changed to 0's. I also used xl2003. And I tested a few times before my original post--and a couple times with this one. Maybe it was a differences in the type of broken formulas we used??? And when it comes down to wisdom, I'll defer to yours <bg. Tom Ogilvy wrote: 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 -- Dave Peterson The difference is that what you did caused the #REF! to appear in the formula. When you deleted column A and row 1, it changed references to any deleted cells to #REF! within the formula, resulting in the cell returning #REF!. Since the #REF! was in the formula, your method worked. However, in most cases, #REF! errors are caused by the inability to find a valid source, not by #REF! being in the formula. For example, if I have a formula =vlookup(A1,[Summary.xls]Sheet1! A1,1,0) and I don't have Summary.xls open, the cell will return #REF!, but there won't be a #REF! in the formula. In this case, your method won't work because the error is in the cell, not the formula. |
Find #Ref! and replace
Dave,
I see Yes - we are talking about apples and oranges. I produced my Ref errors by a formula that produced a bad reference - the actual formula itself did not have #REF! in it as you show (using indirect). I guess it depends how the errors are produced for the OP. I envisioned use of Indirect in a worksheet where either one or both of the sheets might be there. But if they were there and one gets deleted, then we are on your side of the court. Thanks for expanding my horizons on this one. <g -- Regards, Tom Ogilvy "Dave Peterson" wrote: I put a bunch of formulas starting in D6: =A1+B1+C1 =B1+C1+D1 =C1+D1+E1 =A2+B2+C2 =B2+C2+D2 =C2+D2+E2 =A3+B3+C3 =B3+C3+D3 =C3+D3+E3 =A4+B4+C4 =B4+C4+D4 =C4+D4+E4 =A5+B5+C5 =B5+C5+D5 =C5+D5+E5 Then I deleted column A and row 1: My formulas changed to: =#REF!+#REF!+#REF! =#REF!+#REF!+#REF! =#REF!+#REF!+#REF! =#REF!+A1+B1 =A1+B1+C1 =B1+C1+D1 =#REF!+A2+B2 =A2+B2+C2 =B2+C2+D2 =#REF!+A3+B3 =A3+B3+C3 =B3+C3+D3 =#REF!+A4+B4 =A4+B4+C4 =B4+C4+D4 =#REF!+A5+B5 =A5+B5+C5 =B5+C5+D5 And then ran that line of code. My formulas that were broken were changed to 0's. I also used xl2003. And I tested a few times before my original post--and a couple times with this one. Maybe it was a differences in the type of broken formulas we used??? And when it comes down to wisdom, I'll defer to yours <bg. |
Find #Ref! and replace
To both Tim and Tom.
I just figured that the OP would make the simple kind of mistakes (mine) rather than the more complex kind of mistakes (yours) <vbg. Ps to Tim. Just because the workbook is closed shouldn't be the reason the =vlookup() returned a Ref error. (Not important in this discussion, but I'm kind of anal compulsive.) But I am kind of curious to what caused the Ref error for the OP. My money is on #7 in the 5th. Oh, wait. That's a different bet! Tom Ogilvy wrote: Dave, I see Yes - we are talking about apples and oranges. I produced my Ref errors by a formula that produced a bad reference - the actual formula itself did not have #REF! in it as you show (using indirect). I guess it depends how the errors are produced for the OP. I envisioned use of Indirect in a worksheet where either one or both of the sheets might be there. But if they were there and one gets deleted, then we are on your side of the court. Thanks for expanding my horizons on this one. <g -- Regards, Tom Ogilvy "Dave Peterson" wrote: I put a bunch of formulas starting in D6: =A1+B1+C1 =B1+C1+D1 =C1+D1+E1 =A2+B2+C2 =B2+C2+D2 =C2+D2+E2 =A3+B3+C3 =B3+C3+D3 =C3+D3+E3 =A4+B4+C4 =B4+C4+D4 =C4+D4+E4 =A5+B5+C5 =B5+C5+D5 =C5+D5+E5 Then I deleted column A and row 1: My formulas changed to: =#REF!+#REF!+#REF! =#REF!+#REF!+#REF! =#REF!+#REF!+#REF! =#REF!+A1+B1 =A1+B1+C1 =B1+C1+D1 =#REF!+A2+B2 =A2+B2+C2 =B2+C2+D2 =#REF!+A3+B3 =A3+B3+C3 =B3+C3+D3 =#REF!+A4+B4 =A4+B4+C4 =B4+C4+D4 =#REF!+A5+B5 =A5+B5+C5 =B5+C5+D5 And then ran that line of code. My formulas that were broken were changed to 0's. I also used xl2003. And I tested a few times before my original post--and a couple times with this one. Maybe it was a differences in the type of broken formulas we used??? And when it comes down to wisdom, I'll defer to yours <bg. -- Dave Peterson |
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 |
| All times are GMT +1. The time now is 10:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com