VBA inserting formula into cells

[scoha]

I'm getting befuddled with my ""bunny ears" Please help me get the bunny ears in the right place for this line, objective is to insert the formula, =if(C&LastRowStaff="","", Row()-Row($a$8)+1) into the cell.

I have got this far but theres something tricky wrong with the syntax:

.Cells(LastRowStaff,"A").Formula="=if(c"&LastRowStaff&"="""","""",Row()-Row($a$8)+1)"

 

[pbornemeier]

The formula, as is, caused Excel some heartburn. I believe this is what you want:
"=IF(INDIRECT(""C""&LastRowStaff)="""","""", ROW()-ROW(R8C1)+1)"

The easiest way to get the VBA version of a working cell formula is to turn on the macro recorder then click on the cell, click in the formula bar and press Enter. The appropriate VBA code is recorded:

Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF(INDIRECT(""C""&LastRowStaff)="""","""", ROW()-ROW(R8C1)+1)"

This will always evaluate to Row():
Row()-Row($a$8)+1

 

[scoha]

Wow Phil that's brilliant and thanks for the great tip.

 

[Peter_SSs]

I'm a little confused about what you have ended up with? It seems to me that the suggested code, as it stands, will just produce a #NAME? error in whatever cell it is placed. Presumably you modified it in some way?

Doesn't your original attempt work if you include a few spaces to separate the variable name, ampersands etc?

.Cells(LastRowStaff, "A").Formula = "=if(c" & LastRowStaff & "="""","""",Row()-Row($a$8)+1)"

Also, be aware that INDIRECT is a volatile function so can affect sheet performance if used a lot.

Or have I completely missed what this questuon is about?

 

[pbornemeier]

Peter is correct in that I misread your intial post. You should get your original formula working correctly in a cell then use the technique I mentioned to get the VBA-creatable version of it.

This seems to be the correct translation ofwhat was in pots #1, but the false portion of the IF statement will still always evaluate to ROW()

.Cells(LastRowStaff,"A").FormulaR1C1 = "=IF(""C""&LastRowStaff="""","""",ROW()-ROW(R8C1)+1)"

 

[Peter_SSs]

This seems to be the correct translation ofwhat was in pots #1,

.Cells(LastRowStaff,"A").FormulaR1C1 = "=IF(""C""&LastRowStaff="""","""",ROW()-ROW(R8C1)+1)"

For me, that still results in #NAME? in the cell.

.. but the false portion of the IF statement will still always evaluate to ROW()

How so? If the formula was placed in cell A100, then ROW() would be 100 but the False part of the formula would be
100-8+1
=93

 

[pbornemeier]

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 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"

 

[Peter_SSs]

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.

 

[pbornemeier]

I didn't think about the circumstance where rows would be inserted. Thanks for helping me learn about this possibility.