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
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...

-Sheeloo
-------------------------------------
Pl. click 'Yes' if this was helpful...

"Lynne" wrote:

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 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.

"Sheeloo" wrote:

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...

-Sheeloo
-------------------------------------
Pl. click 'Yes' if this was helpful...

"Lynne" wrote:

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

Just some guesses...

Edit|replace (or ctrl-h) is limited to the usedrange. So if your data doesn't
go down far enough--or not far enough to the right, then excel may not find
anything to change.

You can check the used range by hitting ctrl-end. If that takes you past what
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 the
used range by typing something in that bottom right cell (and finish the entry
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 space
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 that
character(s) is:
http://www.cpearson.com/excel/CellView.aspx

Depending on what that character is, you may be able to use alt-#### (from the
number keypad) to enter the character into the Other box in the text to columns
wizard dialog.

In fact, you may be able to select the character (in the formula bar), and copy
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 he
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:

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.

"Sheeloo" wrote:

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...

-Sheeloo
-------------------------------------
Pl. click 'Yes' if this was helpful...

"Lynne" wrote:

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

--

Dave Peterson

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?

"Dave Peterson" wrote:

Just some guesses...

Edit|replace (or ctrl-h) is limited to the usedrange. So if your data doesn't
go down far enough--or not far enough to the right, then excel may not find
anything to change.

You can check the used range by hitting ctrl-end. If that takes you past what
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 the
used range by typing something in that bottom right cell (and finish the entry
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 space
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 that
character(s) is:
http://www.cpearson.com/excel/CellView.aspx

Depending on what that character is, you may be able to use alt-#### (from the
number keypad) to enter the character into the Other box in the text to columns
wizard dialog.

In fact, you may be able to select the character (in the formula bar), and copy
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 he
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:

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.

"Sheeloo" wrote:

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...

-Sheeloo
-------------------------------------
Pl. click 'Yes' if this was helpful...

"Lynne" wrote:

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

--

Dave Peterson

If you got 0 from =len(a1), then A1 didn't contain any characters.

Did you mistype your response?



Lynne wrote:

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?

"Dave Peterson" wrote:

Just some guesses...

Edit|replace (or ctrl-h) is limited to the usedrange. So if your data doesn't
go down far enough--or not far enough to the right, then excel may not find
anything to change.

You can check the used range by hitting ctrl-end. If that takes you past what
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 the
used range by typing something in that bottom right cell (and finish the entry
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 space
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 that
character(s) is:
http://www.cpearson.com/excel/CellView.aspx

Depending on what that character is, you may be able to use alt-#### (from the
number keypad) to enter the character into the Other box in the text to columns
wizard dialog.

In fact, you may be able to select the character (in the formula bar), and copy
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 he
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:

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.

"Sheeloo" wrote:

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...

-Sheeloo
-------------------------------------
Pl. click 'Yes' if this was helpful...

"Lynne" wrote:

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

--

Dave Peterson


--

Dave Peterson

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?

"Sheeloo" wrote:

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...

-Sheeloo
-------------------------------------
Pl. click 'Yes' if this was helpful...

"Lynne" wrote:

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

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...



"Lynne" wrote:

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?

"Sheeloo" wrote:

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...

-Sheeloo
-------------------------------------
Pl. click 'Yes' if this was helpful...

"Lynne" wrote:

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

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.

"Sheeloo" wrote:

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...



"Lynne" wrote:

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?

"Sheeloo" wrote:

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...

-Sheeloo
-------------------------------------
Pl. click 'Yes' if this was helpful...

"Lynne" wrote:

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

What did you find from Chip's addin?

What happened when you ran that macro to clean up the code?

Lynne wrote:

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.

"Sheeloo" wrote:

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...



"Lynne" wrote:

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?

"Sheeloo" wrote:

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...

-Sheeloo
-------------------------------------
Pl. click 'Yes' if this was helpful...

"Lynne" wrote:

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

--

Dave Peterson