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]

Use indirect

e.g.:

= COUNTIF(INDIRECT("E37:I43"),INDIRECT("E4"))

 

[AndyJR]

Hi sheetspread,

i going to try, the problem is that there are 5000+ formulas in the worksheet.


Thank you so much

 

[Smitty]

Welcome to the Board!

If you set your data up as a table you can use Structured References, which will be dynamic and won't care about the number of rows you have.

Here's a great new blog post by Jon Peltier on the subject: Structured Referencing to Identify Parts of Excel Tables - Peltier Tech Blog

 

[sheetspread]

the problem is that there are 5000+ formulas in the worksheet.

I think you can change the formulas en masse via ctrl-h without VBA, but there's always that option. Smitty's suggestion sounds better anyway.

 

[AndyJR]

Hi Mr Smitty,

Yes sir, i realised that it's much better as a table. (and easier)
but i can't apply as, due that the person than i'm helping on this project need to extract selected ranges to create 11 full Reports on another sheet.
Then each of the reports are going to be sorted on different way (by column).

i'll save the blog link to read it and learn from it!

Thank you !!!!

AndyJr.

 

[AndyJR]

Hi sheetspread,

Yeah, you're right about to change/rename mass formula.. i haven't play with excel for over 8 years.. many new features in excel, wow is amazing


Thanks !

AndyJr

 

[Smitty]

but i can't apply as, due that the person than i'm helping on this project need to extract selected ranges to create 11 full Reports on another sheet.

That's not a problem, just create Pivot Tables for the reports. Tables are great for that in that Pivot Tables read the table range dynamically as well, so if you add new data you just refresh the Pivot Tables.

 

[AndyJR]

Hi Smitty,

I found an small code that contain the INDIRECT that supposedly perform what i need.
but i have no idea when to added, when call the file, when perform the command button?
I added my range and receive an error: "expected end of statement"

Private Sub AddIndirect()
Dim c As Range For Each c In Range("F4:TV14")
c.Formula = "=INDIRECT(" & Right(c.Formula, Len(c.Formula) - 1) & ")"
Next c

End Sub

Thank you


AndyJr

 

[Smitty]

Your For statement needs to be on a new line:

Private Sub AddIndirect()
  Dim c As Range 

  For Each c In Range("F4:TV14")
    c.Formula = "=INDIRECT(" & Right(c.Formula, Len(c.Formula) - 1) & ")"
  Next c

End Sub

As for calling the code, I'd imagine you'd only want to run it one time so you can just hit F5 with the cursor anywhere in the code to run it. Just make sure to do it on a copy of your workbook as code can't be undone.