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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,403
Messages
6,171,921
Members
452,433
Latest member
Woodchuck76

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top