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

AndyJR

Board Regular
Joined
Jun 20, 2015
Messages
90
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
Rich (BB code):
= 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
Rich (BB code):
= 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

 
Last edited:

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
Use indirect

e.g.:

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

AndyJR

Board Regular
Joined
Jun 20, 2015
Messages
90
Hi sheetspread,

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


Thank you so much
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117

ADVERTISEMENT

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

Board Regular
Joined
Jun 20, 2015
Messages
90
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

Board Regular
Joined
Jun 20, 2015
Messages
90

ADVERTISEMENT

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

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Jun 20, 2015
Messages
90
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"

Code:
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

Legend
Joined
May 15, 2003
Messages
29,536
Your For statement needs to be on a new line:

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,951
Messages
5,525,846
Members
409,667
Latest member
jwieting

This Week's Hot Topics

Top