# How to add INDIRECT to all formulas

1. ## How to add INDIRECT to all formulas

I have a workbook full of cell references and I need to add INDIRECT to all formulas. For example, I'd like the following formula...

=Table1!E6

... to become ...

=INDIRECT("Table1!E6")

I've done this before by finding and replacing the equals sign with =INDIRECT(" and then manually pasting in ") after every formula, but my current workbook is quite large. Any suggestions would be MUCH appreciated.

Thanks!

2. ## Re: How to add INDIRECT to all formulas

Try this

``Please Login or Register  to view this content.``

3. ## Re: How to add INDIRECT to all formulas

Thanks! That was much more efficient that what I was doing. I found some similar code to help me add the quotes and parentheses at the end but this worked all in one step!

Now I'm trying to tackle a similar but slightly more complicate issue. I have INDIRECT in all my formulas, as needed, so now they look like this...

=INDIRECT("Table1a!N4")

I'd like to change all formulas in several columns so that the cells appear blank if the value in Column F (of that same row) equals 0. If values are 0 in column F, then the metrics I've included in the other columns don't apply, so I'd like the cells to be empty, rather than 0, so they are not interpreted as meaningful. So, I'd like the data set to look like this:

Column F, Column G, Column H
10, 8 , 9
15, 10, 14
0
10, 9, 7
0
20, 15, 17

So if the formula shown above was in Column H Row 3, I would want it to change to the following...

=IF(F3=0,"",INDIRECT("Table1a!N4"))

Is there some way I can alter the code you provided above to make this work? I tried messing with it myself but I was only able to get the extra close parentheses at the end. I'm new to VBA so this is probably well beyond my current skill level.

Thank you again for your time and assistance!

4. ## Re: How to add INDIRECT to all formulas

I've been messing with it more and was able to get part of it figured out. The following gives me everything I want except it just looks to see if cell F999=0 for all cells replaced, rather than for that specific row

Sub Test()
Dim Cell As Range
For Each Cell In Cells.SpecialCells(xlCellTypeFormulas, 23)
Cell.Formula = "=IF(F999=0,""""," & Mid(Cell.Formula, 2, Len(Cell.Formula) - 1) & ")"
Next Cell
End Sub

5. ## Re: How to add INDIRECT to all formulas

For anyone else who may be interested, I was able to get it working...

Sub Test()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = "=IF(R[0]C6=0,""""," & Mid(Cell.Formula, 2, Len(Cell.Formula) - 1) & ")"
Next Cell
End Sub

There are currently 1 users browsing this thread. (0 members and 1 guests)