Inserting formula using VBA

[Andman0]

Hi All,

I'm trying to make a command which automatically inserts a row into the pictured sheet below with formulas in each cell, and I'm not quite sure how to have the command input the right cell reference into the formulas; I want it to reference a cell in the active cell's row and a specific column.

For instance, the formula in cell A4 is: =ROW(A4)-(1+COUNTBLANK(OFFSET(A4,ROW($A$1)-ROW(A4),0):OFFSET(A4,-1,0))), which calculates the number of the line item, not including the top row or any header rows.
When I hit the "insert row" command button with the current active cell somewhere in row 4, I'd want it to create a new row with that formula in A4, and if the active cell were in row 5 I'd want the A4 references to be A5.

I'm rather new to VBA so I apologize if this is trivial to accomplish and I'm just missing something, thank you!

 

[Micron]

Wouldn't it be simpler if A2 on down had =Row()-1 as a formula, then if the code inserts a row, set the cell formula to =Row()-1?
Your formula looks much more complicated for the same thing. However, I chose -1 because you only show 1 header row. I'm guessing your existing code already handles the row insertion, so the missing piece is setting the cell formula in A in that row?
Maybe I'm not grasping the whole picture...

 

[Andman0]

Wouldn't it be simpler if A2 on down had =Row()-1 as a formula, then if the code inserts a row, set the cell formula to =Row()-1?
Your formula looks much more complicated for the same thing. However, I chose -1 because you only show 1 header row. I'm guessing your existing code already handles the row insertion, so the missing piece is setting the cell formula in A in that row?
Maybe I'm not grasping the whole picture...

Hi,

I'm sorry, I should have mentioned that there could be any number of headers, as there's another button which inserts a header with text in column D. I have the countblank there to count up the number of headers above so the line item numbers don't include them in the numbering. I used to use direct cell references for the numbering, but changed it to offset because using the direct references would give a #REF error if I deleted a line or would mess up the numbering if I inserted a row manually, and I'd have to copy the formula from another cell.

I was able to simplify the formula a bit more, looking at it again I was a bit excessive with the offset use as I didn't realize I could just use $A$1 for the countblank range beginning since it will never be deleted: =ROW(A2)-(1+COUNTBLANK($A$1:OFFSET(A2,-1,0))).

My existing code already inserts a row in the activecell's row, I just don't know how to insert a formula with cell references in VBA.

Private Sub CommandButton4_Click()
Call AddBlankRow(ActiveCell.Row)
End Sub

Sub AddBlankRow(in_row)
    Rows(in_row).Select
    Selection.Insert Shift:=xlDown
End Sub

 

[Micron]

Assuming your code activates the cell in the inserted row, you're asking for this sort of thing then?
ActiveCell.FormulaR1C1 ="=ROW(A2)-(1+COUNTBLANK($A$1:OFFSET(A2,-1,0))))

 

[Andman0]

Assuming your code activates the cell in the inserted row, you're asking for this sort of thing then?
ActiveCell.FormulaR1C1 ="=ROW(A2)-(1+COUNTBLANK($A$1:OFFSET(A2,-1,0))))

Yes kind of, however I'd want it to insert with a reference to the cell in whichever row the original active cell is in. So for row 2 it would insert =ROW()-(1+COUNTBLANK($A$1:OFFSET(A2,-1,0))), but in row 4 it would insert =ROW()-(1+COUNTBLANK($A$1:OFFSET(A4,-1,0))).

 

[Micron]

You described this as happening when a cell is active so you should be able to first get the row number using Row()?
Not sure if you can concatenate as in
:OFFSET(A & (ROW(),-1,0))) or set Row() to a variable and concatenate that
:OFFSET(A & lngRow & ,-1,0)))

I would have to try. Not sure I have the time right now - have to go and get my snow tires put on soon.
EDIT - I think you're using a button to activate code so you'd likely have to use ActiveCell.Row, not just Row or Row()

 

[Micron]

OK, didn't take long to try. This seems to work, unless maybe not for you because I'm not inserting rows, just making the formula base itself on the active row

ActiveCell.Formula = "=ROW()-(1+COUNTBLANK($A$1:OFFSET(A" & ActiveCell.Row & ",-1,0)))"

 

[Andman0]

OK, didn't take long to try. This seems to work, unless maybe not for you because I'm not inserting rows, just making the formula base itself on the active row

ActiveCell.Formula = "=ROW()-(1+COUNTBLANK($A$1:OFFSET(A" & ActiveCell.Row & ",-1,0)))"

This works great! I should be able to use that method to insert row numbers into the other formulas in the row's cells as well, thank you!

 

[Micron]

Glad to see that you have a solution!
Thanks.