Clearing cell based on criteria

**Pasha81** · 03-19-2010, 06:37 AM

Hi guys

I've got a pretty straightforward question. I should have been able to figure this out already but for some reason cannot...hopefully someone here can help out!

I have a column of data, lets say Column D.
Some values in Column D have the value ' #DIV/0! '
this is because the formula gave an error, so I want this changed to a blank cell.

Something like

For each cell in Column D
If cell.Value = "#DIV/0!" Then cell.Value = ""

Any thoughts?

thanks

**Marcol** · 03-19-2010, 06:51 AM

Try in your sheet

=IF(B2=0,"",A2/B2)

Where B2 is your divisor

Or if you are using VBa

Please Login or Register  to view this content.

to aviod same error

**Pasha81** · 03-19-2010, 07:24 AM

? I'm not sure what you're trying to do.

I need to change the cell to blank or "", if the value is "#DIV/0!" (which it will be as this column is copied from somewhere else- a pivot table)

thanks for the attempt though

**DonkeyOte** · 03-19-2010, 07:32 AM

Assuming all errors are to be corrected then perhaps simply:

Please Login or Register  to view this content.

(above assumes the cells are values only - if formulae adjust Constants to Formulas but note the formulae will be overridden)

**Marcol** · 03-19-2010, 08:19 AM

Hi Pasha

? I'm not sure what you're trying to do.

I'm suggesting that you fix the error at source, not try to work round it once it happens.

Cheers

**Pasha81** · 03-19-2010, 08:21 AM

thanks Don, works great!

Clearing cell based on criteria

LinkBack

Thread Tools

Rate This Thread

Display

Clearing cell based on criteria

Re: Clearing cell based on criteria

Re: Clearing cell based on criteria

Re: Clearing cell based on criteria

Re: Clearing cell based on criteria

Re: Clearing cell based on criteria

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions