J
jln via OfficeKB.com
How can i in code find alll cells that have #Ref! and replace it with 0?
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
Assuming that would be the only error in the cells selected:
On Error Resume Next
Selection.SpecialCells(xlFormulas,xlErrors) = 0
On Error goto 0
On Error Resume Next
Selection.SpecialCells(xlFormulas,xlErrors) = 0
On Error goto 0
B
Brian
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
Do this:
Cells.Replace What:="#REF!", Replacement:="0", LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=False
Brian Herbert Withun
G
Guest
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.
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.
D
Dave Peterson
Another option based on Brian's suggestion:
Cells.Replace What:="*#REF!*", _
Replacement:="0", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Cells.Replace What:="*#REF!*", _
Replacement:="0", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
G
Guest
?????
--
Regards,
Tom Ogilvy
--
Regards,
Tom Ogilvy
Dave Peterson said:Another option based on Brian's suggestion:
Cells.Replace What:="*#REF!*", _
Replacement:="0", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
D
Dave Peterson
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?
and in my tests, the cells that contained any #REF! errors were changed to 0's.
Or did you mean something else?
G
Guest
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.
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.
D
Dave Peterson
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>.
=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 said: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.
T
Tim Shnell
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>.
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.
G
Guest
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>
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>
D
Dave Peterson
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
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
J
jln via OfficeKB.com
Tom like always it worked great.
Tom said:Assuming that would be the only error in the cells selected:
On Error Resume Next
Selection.SpecialCells(xlFormulas,xlErrors) = 0
On Error goto 0
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.