Hi,

I have a file with literally hundredths of formulas and I need to do the
same change in all of them; if a certain cell is empty the formulas shouldn´t
display anything at all.

Is there any way I can change all formulas at once???? I cannot click and
drag because every formula does a different thing.

thanks!

Hi,

What is the formula?


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"John Pivot Table" wrote:

Hi,

I have a file with literally hundredths of formulas and I need to do the
same change in all of them; if a certain cell is empty the formulas shouldn´t
display anything at all.

Is there any way I can change all formulas at once???? I cannot click and
drag because every formula does a different thing.

thanks!

I think this macro may do what you want...

Sub FixFormulas()
Dim C As Range
For Each C In Selection
C.Formula = "=IF(B4="""",""""," & Mid(C.Formula, 2) & ")"
Next
End Sub

Just change the B4 cell reference to whatever cell you want to test for
empty, then select all the cells you want to make this change to and then
run the macro. You might try this out on a copy of your worksheet first as
the changes it makes cannot be undone.

--
Rick (MVP - Excel)


"John Pivot Table" wrote in
message ...
Hi,

I have a file with literally hundredths of formulas and I need to do the
same change in all of them; if a certain cell is empty the formulas
shouldn´t
display anything at all.

Is there any way I can change all formulas at once???? I cannot click and
drag because every formula does a different thing.

thanks!

Are all the original formula same?
Can you show the original formula and the changed formula

This might need a marco to do it.



"John Pivot Table" wrote:

Hi,

I have a file with literally hundredths of formulas and I need to do the
same change in all of them; if a certain cell is empty the formulas shouldn´t
display anything at all.

Is there any way I can change all formulas at once???? I cannot click and
drag because every formula does a different thing.

thanks!

Every cell has a different formula, but I need almost every single one to
start like this:

=IF($B16="","",ORIGINAL FORMULA)


Hopefully the B16 will change according to the row...



"Shane Devenshire" wrote:

Hi,

What is the formula?


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"John Pivot Table" wrote:

Hi,

I have a file with literally hundredths of formulas and I need to do the
same change in all of them; if a certain cell is empty the formulas shouldn´t
display anything at all.

Is there any way I can change all formulas at once???? I cannot click and
drag because every formula does a different thing.

thanks!

Worked great, Rick!

Thanks!


"Rick Rothstein" wrote:

I think this macro may do what you want...

Sub FixFormulas()
Dim C As Range
For Each C In Selection
C.Formula = "=IF(B4="""",""""," & Mid(C.Formula, 2) & ")"
Next
End Sub

Just change the B4 cell reference to whatever cell you want to test for
empty, then select all the cells you want to make this change to and then
run the macro. You might try this out on a copy of your worksheet first as
the changes it makes cannot be undone.

--
Rick (MVP - Excel)


"John Pivot Table" wrote in
message ...
Hi,

I have a file with literally hundredths of formulas and I need to do the
same change in all of them; if a certain cell is empty the formulas
shouldn´t
display anything at all.

Is there any way I can change all formulas at once???? I cannot click and
drag because every formula does a different thing.

thanks!

On Dec 6, 4:36*am, John Pivot Table <John Pivot
wrote:
Every cell has a different formula, but I need almost every single one to
start like this:

=IF($B16="","",ORIGINAL FORMULA)

Hopefully the B16 will change according to the row...



"Shane Devenshire" wrote:
Hi,

What is the formula?

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

"John Pivot Table" wrote:

Hi,

I have a file with literally hundredths of formulas and I need to do the
same change in all of them; if a certain cell is empty the formulas shouldn´t
display anything at all.

Is there any way I can change all formulas at once???? I cannot click and
drag because every formula does a different thing.

thanks!- Hide quoted text -

- Show quoted text -

Try this macro,

Sub AddIf()

For Each C In Selection
C.Formula = "=IF(B16=""""," & Right(C.Formula, Len(C.Formula)
- 1) & ",0)"
Next C
End Sub

Hope this help

cheers