Finding 0s in Excel 97 and Excel 2002

  • Thread starter Thread starter Evi
  • Start date Start date
E

Evi

In Excel 97, if I try to use the Find box to find a 0 that is the result of
a formula (=8-8), it doesn't find it (though it will find 0 that is typed
into a cell or other numbers that are the result of a formula).

In Excel 2002, I can find the 0 by choosing the Value option in the Find box
but if I try to find the same cell using the Find And Replace box (so that I
can replace all those 0s with another number rather than with a formula that
results in 0), the Value option is no longer there and suddenly Excel can no
longer find the 0.

Any ideas what is going on and is there a way I can find my 0s in Excel 97
and replace them in Excel 2002.

Evi
 
Sorry, in the bit about 97 I accidentally used the Find Replace box to do my
Find. I could find the 0 with the Find box if I chose Find Values and Whole
Cell. But I still want to know how to find 0s in order to replace them.
Evi
 
Try this:

Public Sub Test()
Dim MyCell As Range

For Each MyCell In Range("A:IV")

If MyCell.Formula <> "" And MyCell.Value = 0 Then MyCell.Value = 333

Next MyCell

End Sub

Obviously, 333 can be changed to whatever you want.

There is a name for the area in the worksheet containing cells that have
something in them, but I cannot remember what it is! In the meantime I have
used Range ("A:IV") which is thorough but inefficient.

GB
 
There is a name for the area in the worksheet containing cells that have
something in them, but I cannot remember what it is!
Click to expand...

Aah yes:

For Each MyCell In ActiveSheet.UsedRange

GB
 
That's wonderful. Exactly what I wanted.
Evi
GB said:
Try this:

Public Sub Test()
Dim MyCell As Range

For Each MyCell In Range("A:IV")

If MyCell.Formula <> "" And MyCell.Value = 0 Then MyCell.Value = 333

Next MyCell

End Sub

Obviously, 333 can be changed to whatever you want.

There is a name for the area in the worksheet containing cells that have
something in them, but I cannot remember what it is! In the meantime I have
used Range ("A:IV") which is thorough but inefficient.

GB


do Excel
Click to expand...
 
Back
Top