 # replacing cells with formulas with conditional check AND formula

• Hi - can't figure how to solve following (mentally) simple operation:

Given a range of cells with formulas of form, say, [=x1-y1] and [=x1/y1], i want to replace those cells with =if(a1="", "",[x1-y1]), ie replace original formula with a conditional check and original formula if condition met. As i need to convert a fair amount of such cells it would make sense to have a macro i can just apply to a cell, ie take content of cell, eg [=x1-y1], and replace with "=if(a1="","", x1-y1).

I created a macro, but rather that taking the formula in the cell i am applying it to, it takes the formula from the cell which i developed the macro on and apply that (except columns changed). Logically, what macro should do is take formula in cell, store in stack,replace with =if(a1="", "", insert original formula from stack).

Inputs appreciated.

Thanks.

• Re: replacing cells with formulas with conditional check AND formula

Can you just upload a small sample workbook clearly showing before/after?

• Re: replacing cells with formulas with conditional check AND formula

Hi - have attached two sheets, named before and after. If you look at last row columns D and E you can see what i mean; looking forward to hearing your feedback.

## Files

• Re: replacing cells with formulas with conditional check AND formula

OK
According to the formula in the last row of after.

• Re: replacing cells with formulas with conditional check AND formula

Hi Jindon,

Thanks - the enclosed sheets were simply to illustrate issue rather than being the full sheets i am working on; these sheets contains 100's of columns operating on data as well as using dependent columns, ie the pattern you kindly mention are very specific and the formulas i enclosed were for illustration purposes.

Reiterating my problem: my intention is to take [existing cell formula] (which can have any form, eg x+y, x/y, x*y, avg(), etc) and replace with expression =if(a1="","",[existing cell formula]); in other words trying to hard code the specific contents would not solve the issue, rather it would simply push the coding from the sheet to VB as would have to define each and every column formula.

Makes sense?

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!