Macro to remove zerro

  • Thread starter Thread starter Newfie809
  • Start date Start date
N

Newfie809

I have a spread sheet with about 300 formulas and the formula is looking up
information and returning the answer from another workbook, but some of the
cells that do not have any information the fromula returns a "0" is there a
macro or a formula that if there is no information in that cell it is
returned blank?

thank you for you help
 
On some of my spreadsheets that use a bunch of formulas i use conditional
formatting to hide the zeroes:

Format > Conditional Format:

Cell Value is: Equal to: 0

and my text color is white.

The cell value will remain 0, but for display purposes it'll show up blank.

Now if you're looking for a code to replace a returned value of 0 for
another value, then that's a different story.
 
Hi Adam,

What I am looking for is the cell returns nothing. When something is in the
cell it will count as a .33 I have 4 columns and in each cell of the 4
columns there is a formula that will return a .33 and that will add up to 133
and I only want it to add up to 100 therefore the cell should return with
nothing because even with the zerro the cell thinks there is something there
and it will return a .33. I don't know if I amy explaining it correctly do
you understand what I am trying to do. I could delete the zerro but then I
am deleting the formula also and I need the formula there, just in case on
the other spread sheet the information changes in each column.


Thank you for your response.
 
A cell with a formula can never return "nothing".

You can make it look blank by trapping

=IF(A1="","",A1)

Where have you arbitrarily set a cell to return .33 when not completely
blank?

I think that is where you should be doing the correction.


Gord Dibben MS Excel MVP
 
Hi Gary,

It returned the cell and it looks blank, but it still count the cell as if
there is something there and the cells that had something in them are now
also blank the infromation that was there is now blank. But thank you for
your help it solve part of the problem.
 
Hi Gary,

Just to let you know that I got the formula to work as for bringing back the
information in the cell, and it works now. But the blank cell still acts as
if there is something in the cell and counts it in the total.

thanks again for your help
 
Yes, I think you are correct. I ws thinking about it last night and decided
to try something in the sum column.

B C (D E F) G

ENG4C1 ENG3U1 ENG3U1 0 0 0 = 1.33

Here is the formula that I was using to add the 6 columns.
=ROUND((COUNTIF(B8,"<>")+COUNTIF(C8,"<>")+COUNTIF(G8,"<>")+MIN(COUNTIF(D8:F8,"<>"),1))/3,2.
So I need to adjust this column to get the result I would like.

thanks
 
Back
Top