I had this same problem. I found that if I make the selection of cells into the 'Number' format, then I go to find and replace, then I enter a space in the find what, and a 0 in the replace with box. Then it changes all of the blanks to a 0.
Lynn wrote:
Dave,THanks for the info.
02-Apr-09
Dave
THanks for the info. I am sure it will mean more to me once I lear
something about macros! I am on a mission
:
Previous Posts In This Thread:
find and replace Excel 2007
In 2003 I used to be able to select a range that contained blank cells and
use find and replace to change the blanks to 0 for an array formula to work.
Now 2007 says it can't find any matching data if I leave the find field
blank. Any ideas? There are alot of empty cells that need a 0 value
Thanks
I don't think there is any difference between 2003 and 2007 as far as this
I don't think there is any difference between 2003 and 2007 as far as this
behavior is concerned..
You must have at least one non-blank cell in the range for your method to
work.... If all are blanks then you can simply type 0 and press CTRL-ENTER
after selecting the range..
-Sheelo
------------------------------------
Pl. click 'Yes' if this was helpful..
:
I get a message saying "Excel can not find any data to replace.
I get a message saying "Excel can not find any data to replace." I have
never had this problem occur in past years but this is the first year of
using 2007 for this calculation. I am selecting a range Col. D to P. and a
varying number of rows. It will only make the replace in the first two
columns. i.e. 16 replacements were made when the range held about 60 or 70
blank cells. When I try to select any columns past these, I get the message
above. I can't select individual columns or ranges. This is occurring on
several different workbooks. Any other ideas would be much appreciated
:
Just thought of something to add.
Just thought of something to add. These numbers were copied and pasted from
a downloaded mark sheet from a website that holds my students marks. Is it
possible that there is something behind the cell that is blocking the blank
value from being read. If I clear content in the cell the replacement will
work. If this is the case, is there a way to clear content from blank cells
only in a range
:
Just some guesses...Edit|replace (or ctrl-h) is limited to the usedrange.
Just some guesses..
Edit|replace (or ctrl-h) is limited to the usedrange. So if your data doesn'
go down far enough--or not far enough to the right, then excel may not fin
anything to change
You can check the used range by hitting ctrl-end. If that takes you past wha
you need, then this isn't the problem
But if it doesn't take you far enough down and to the right, you can extend th
used range by typing something in that bottom right cell (and finish the entr
with an enter). Then hit the delete key to clear the contents of that cell.
Test to see if it worked by hitting ctrl-end
My other guess is that you have something in those cells. It could be spac
characters or even those non-breaking HTML codes (char(160)'s).
If you use a formula like
=len(a1
(where A1 is one of the offending cells
do you see 0
I saved this from a previous post. You may find it useful
Chip Pearson has a very nice addin that will help determine what tha
character(s) is
http://www.cpearson.com/excel/CellView.asp
Depending on what that character is, you may be able to use alt-#### (from th
number keypad) to enter the character into the Other box in the text to column
wizard dialog
In fact, you may be able to select the character (in the formula bar), and cop
it. Then use ctrl-v to paste into that text to columns Other box
You may be able to use Edit|Replace to change the character--Some characters can
be entered by holding the alt-key and typing the hex number on the numeric
keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've
never been able to get alt-0013 to work for carriage returns.
Another alternative is to fix it via a formula:
=substitute(a1,char(##),"")
Replace ## with the ASCII value you see in Chip's addin.
Or you could use a macro (after using Chip's CellView addin):
Option Explicit
Sub cleanEmUp()
Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long
myBadChars = Array(Chr(##)) '<--What showed up in CellView?
myGoodChars = Array("")
If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If
For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr
End Sub
If you're new to macros:
Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html
David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm
(General, Regular and Standard modules all describe the same thing.)
Lynne wrote:
--
Dave Peterson
See Dave's suggestion...Also try =Code(A1)changing A1 to some of the cells..
See Dave's suggestion...
Also try
=Code(A1)
changing A1 to some of the cells..
YOu can check the LEN of the cells
Try F5->Spcecial...
BUT remember there is NO difference in behavior because of Excel 2007...
:
Thanks for the suggestions. The range test showed not to be a problem.
Thanks for the suggestions. The range test showed not to be a problem. But
when I did the =len(a1) I did get 0 in that cell. That means there are
characters behind, right? Can these me removed all at once?
:
Used f5 -> special and the cells in the sheet are definately not blank.
Used f5 -> special and the cells in the sheet are definately not blank. I
applied the addin as Dave suggested and found hex code for the cells but can
only still replace one cell at a time instead of the selected range.
:
If you got 0 from =len(a1), then A1 didn't contain any characters.
If you got 0 from =len(a1), then A1 did not contain any characters.
Did you mistype your response?
Lynne wrote:
--
Dave Peterson
What did you find from Chip's addin?
What did you find from Chip's addin?
What happened when you ran that macro to clean up the code?
Lynne wrote:
--
Dave Peterson
I did get 0, but the f5 special did not select those cells as blank in the
I did get 0, but the f5 special did not select those cells as blank in the
sheet. I downloaded the sheet from the website as a PDF and converted it to
a word document and in each 'offending' cell there was a little box, looked
like a text box, that I could select and delete. Whatever that empty little
box is, I believe, is stopping find and replace from recognizing it as empty.
I have never used macros before but I did get the add in and there is
something in the cell. The variable said string and double and the hex code
was x20 and x30. But that is as far as I got before my lunch hour ended. I
will have to learn about macros! Thanks for your interest and assistance.
:
I don't have a guess why you get 0 for a length if you see those little boxes.
I don't have a guess why you get 0 for a length if you see those little boxes.
Once you learn more about macros, try this to clean up those boxes:
Option Explicit
Sub cleanEmUp()
Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long
myBadChars = Array(Chr(20), chr(30)) '<--What showed up in CellView?
myGoodChars = Array("", "")
If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If
For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr
End Sub
If you find more of those funny characters, use Chip's addin to determine those
hex values and add them to the array.
And remember to add additional "" to the other array, too!
Lynne wrote:
--
Dave Peterson
Dave,THanks for the info.
Dave,
THanks for the info. I am sure it will mean more to me once I learn
something about macros! I am on a mission!
:
Submitted via EggHeadCafe - Software Developer Portal of Choice
Visual Studio .NET Architecture Templates
http://www.eggheadcafe.com/tutorial...04-c2bc1173d9db/visual-studio-net-archit.aspx