Find and replace based on formula result

Trikageon

Board Regular
Joined
Apr 29, 2010
Messages
51
Hey all!

I'm looking for a bit of code that would seek out a specific value that's displayed as a result of a formula and, once found, clear those cells. For example, my formula is:

=IF($C14="Sub-total",SUMIF($M:$M,"Sum"&KK13,G:G),IF(AND(ISTEXT($F13),ISBLANK($C14)),"",IF(AND(ISBLANK($F12),ISTEXT($F13)),"Box",IF(ISTEXT($F13),"Sum",IF(ISBLANK($F14),"",IF(ISTEXT($F14),$F14,"Box"))))))

All I want to do in my macro is search-out, in a particular worksheet, when the result of the above formula is "Box" and then simply clear those cells with that result. Ideally, if I could do that within a defined range, that would be good too (G:H)!

I can find just those cells with this result using the basic "find" feature, with drop-down "values" selected, but in order to find and replace, it's only giving me the option to select "formulas" from the drop-down, which of course will then find every cell with that word in the *formula* and I don't want that...I just want to find when the result is "Box"

Any ideas?
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

swinglow

Active Member
Joined
Mar 31, 2010
Messages
394
so instead of "Box" you want it to be nothing? Would an empty string suffice? (i.e. "")
because then you could just clear "box" out of your formulas
 

Trikageon

Board Regular
Joined
Apr 29, 2010
Messages
51
I thought about that, but I need it be blank, because it's preventing another formula from adding properly (and I don't really want a bunch of zeros). Unless you can think of a fix to the formula that's having the error:

=IF(ISTEXT(F54),F54,IF(F54="","",F54+G54-H54))

Maybe there's a way for it not to give the result "#Value!" if we changed "box" to ""?

Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,083
Messages
5,526,753
Members
409,717
Latest member
Oscarsalone

This Week's Hot Topics

Top