VBA to Keep Same Formula Row/column Reference after new row is inserted

[AndyJR]

HI all,

I'm using many different formulas with row/columns reference (located in F4: Q14)
the problem that i'm getting is that on daily basis i have to insert a new row (ROW 37) - Data start on row 37 -
after the insertion the Formulas stay but it change the cell/columns reference.

Example:
among many different formulas in cell E4 i'm using these Formula with cell reference

= COUNTIF($E37:$I43,$E4)

then after i Insert on Row 37 and input new information

the formulas row/columns reference (located in F4: Q14) change to

= COUNTIF($E38:$I44,$E4)

And what i need to keep the formula that references the row/column position in the
spreadsheet regardless of if Row are inserted or deleted so that after
inserting a new row.

Note : it happen the same thing using either absolute/Relative reference.


Any help please?


AndyJr

 

[sheetspread]

I don't think that formula is what you're after, in which case try:

Sub AddIndirect()
Dim c As Range
For Each c In Range("f4:tv14")
c.formula = Replace(Replace(Replace(Replace(c.Formula, "COUNTIF(", "COUNTIF(INDIRECT("""), ",", "),INDIRECT("""), "),", """),") & ")", "))", """))")
Next c
End Sub