apply a widespread edit to a formula

[tuffluck]

say i have 10 formulas in 10 different rows. all formulas are different, but i want each one to say something like =if(a1>5,formula,0) but i want each unique formula to be inserted into the "formula" part of my equation. in other words i want to just apply a new formula to a wide array of different formulas and i want that new formula to just be the precursor to the unique formula already in the cell. so just plain ole copy/paste won't do it. for example:

a1 reads =c1*d1
a2 reads =d2/e5*12

and i want them to read:

a1 =if(a10=4,c1*d1,0)
a2 =if(a10=4,d2/e5*12,0)

but i want to be able to write that formula in a1, and then paste the =if() part only into a2. is that possible? otherwise it's a lot of work to change these thousand-something formulas i have in my spreadsheet currently and i would need to hire an admin to do that...just kidding. thanks for the help!

 

[Weaver]

Not sure if this is exactly what you need, but you might be able to work with it.

Sub amendformulas()
    newformula = "=if(a10=4,XXXXXX,0)"
    Set Rng = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
    If Not Rng Is Nothing Then
        For Each r In Rng
            f = Mid(r.Formula, 2, 8000)
            r.Formula = Replace(newformula, "XXXXXX", f)
        Next r
    End If
End Sub

Basically, it goes through all the cells in the current worksheet that have formulas in, copies the formula, removes the "=", then inserts it into the longer version (as defined by 'newformula') and reinserts this new formula.

HTH

 

[passinthru]

I'm not clear on what you're asking, but I'll toss out an idea. If this is a one-time change, you might be able to accomplish your goal with a series of carefully thought out search & replace actions.

 

[tuffluck]

thanks! that sounds pretty awesome to me. dumb question--how can i use "activesheet" to instead only select an array of cells? i.e. a325 thru aq1300

 

[tuffluck]

I'm not clear on what you're asking, but I'll toss out an idea. If this is a one-time change, you might be able to accomplish your goal with a series of carefully thought out search & replace actions.

not a bad thought, but the false side of the if statement would actually be different every time.

what i'm attempting to do is take 50 formulas that are all different and add a constant formula in them. basically that formula is saying if yes then do the formula. if no, then reference another cell--that cell depends on the individual formula and is different for every cell.

 

[Weaver]

thanks! that sounds pretty awesome to me. dumb question--how can i use "activesheet" to instead only select an array of cells? i.e. a325 thru aq1300

you could try:

Set Rng = ActiveSheet.range("A325:AQ1300").SpecialCells(xlCellTypeFormulas)

Apologies for the late response.

 

[Giordano Bruno]

Passinthru may be onto something. If you select the formulas and replace '=' with '=if(a10=4', this will create the first part of the formula and also automatically put in a closing bracket.

Then replace ')' with ',0)' and the job's done.

It won't work if you have any equals signs within your formulas, nor if you already have any closing brackets in them.

Just a thought.

 

[Peter_SSs]

Then replace ')' with ',0)' and the job's done.

The only problem with that is there is no ')' to replace in the original example formulas.

 

[Giordano Bruno]

Hi Peter,

When I do the first bit, Excel puts the closing bracket in for me.

 

[Peter_SSs]

When I do the first bit, Excel puts the closing bracket in for me.

Of course you are quite correct.