I preface all my comments below with acknowledgement that I don't really know exactly what the OP has or wants.
From the left part of the equation in Post #1, I understood that LastRowStaff was a variable in the code that would contain a row number. Before I manually entered the formula in a cell, I defined the named range 'LastRowStaff' as a cell that contained an integer so I could verify the formula would evaluate correctly.
I agree that LastRowStaff is most likely a variable in the code. However, a variable in the code and a named range are two completely different things.
I think I (incorrectly) translated $A$8 as R1C8. I should have said,"the false part will always evaluate as Row()-7, you don't need Row()+(R8C1)-1"
This may not always be the case. See below.
To demonstrate a number of these issues, take a fresh sheet and make a named range LastRowStaff and populate that cell with any value you wish.
Now run this code
<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> InsertFormula()<br> <SPAN style="color:#00007F">Dim</SPAN> LastRowStaff <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br> <br> LastRowStaff = 20<br> <br> <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br> .Cells(LastRowStaff, "C").Value = 1<br> <br> <SPAN style="color:#007F00">'Formula from post #5</SPAN><br> .Cells(LastRowStaff, "D").FormulaR1C1 = "=IF(""C""&LastRowStaff="""","""",ROW()-ROW(R8C1)+1)"<br> <br> <SPAN style="color:#007F00">'Formula from post #4</SPAN><br> .Cells(LastRowStaff, "E").Formula = "=if(c" & LastRowStaff & "="""","""",Row()-Row($a$8)+1)"<br> <br> <SPAN style="color:#007F00">'Formula from post #4 with 'False' section modified</SPAN><br> .Cells(LastRowStaff, "F").Formula = "=if(c" & LastRowStaff & "="""","""",Row()-7)"<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
All 3 formula cells currently show 13. However, ...
1. Can you change the value of the named range, or the value of C20 or the value of any other cell to cause the value in D20 to change? That is, can you ever make the 'True' part of that column D formula operate? If you use Excel's 'Evaluate Formula' feature I think you will see what is happening.
2. Ensure that C20 is not blank, then insert a new row somewhere before row 8. Comparing the column E & F formula results now should demonstrate that putting ROW()-ROW($A$8)+1 in the formula is not quite the same as putting ROW()-7 in the formula.
If LastRowStaff is indeed a named range (which I doubt, but the OP can certainly correct me if I'm wrong), then none of the formulas in the code in this post would be appropriate.