Updating cell formula based on added cell/row value

Question

Here's my dilemma:

I have followed the add new row/cell formula from Ron de Bruin and it works splendidly; however, I am taking the values entered in the new cells and using them in calculations ... which are also in new cells added. I'll past the code I'm referencing below:

    rng.Parent.Cells(LastRow + 1, LastCol + 2).Value = spm
        rng.Cells(LastRow + 1, LastCol + 2).Borders(xlDiagonalDown).LineStyle = xlNone
        rng.Cells(LastRow + 1, LastCol + 2).Borders(xlDiagonalUp).LineStyle = xlNone
        With rng.Cells(LastRow + 1, LastCol + 2).Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With rng.Cells(LastRow + 1, LastCol + 2).Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With rng.Cells(LastRow + 1, LastCol + 2).Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With rng.Cells(LastRow + 1, LastCol + 2).Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        rng.Cells(LastRow + 1, LastCol + 2).Borders(xlInsideVertical).LineStyle = xlNone
        rng.Cells(LastRow + 1, LastCol + 2).Borders(xlInsideHorizontal).LineStyle = xlNone
            With rng.Cells(LastRow + 1, LastCol + 2)
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlBottom
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
            End With
    rng.Parent.Cells(LastRow + 1, LastCol + 3).Locked = False
    Range("K8").Value = tsqs
    Range("L8").Value = spm
    'Calculate value for Man Hours per Item
    rng.Parent.Cells(LastRow + 1, LastCol + 3).Formula = <<still working on this bit>>

So, basically what I am wondering is: 1. How would I add a formula to a cell (new cell in column 3 & 4) which uses the value of the two newly created cells?

Basic Layout:

Row 16, Column A (Preset Text input) | Column B (InputBox generates cell value) | Column C (InputBox generates cell value) | Column D (formula to calculate B * C) | Column E (formula to calculate D * B)

I can get formulas to calculate through VBA, that's not the issue; my issue is that if a user changes the value in any of the newly created cells in Columns B/C, how do I / can I update the formula in the newly created cells in Columns D & E?

I was thinking of using Worksheet.Calculate but I'm not certain that will work on the VBA calculations.

I can post the full Sub() to view if that is needed.

There are a couple of options. You can use the VBA to write formulas like Cell.Formula = "= B3 * C3" or you can mark the calculating function as volatile. — David Rushton
I don't think I'm reading this correctly... you have dynamic calculations in D/E which is formula="=B#*C#"? If this is the case, I don't see the issue, since this would take whatever values are being used in C/B. If I am understanding this wrong, then why not fill in D/E to be dynamic, as such? I do not know if you're able to fill down the calculation to the last row, or if you want only specific rows to have this equation. — Cyril
Thanks for the great responses! I'll try to answer as best I can: @destination-data : I have used the following (which I'm thinking is similar to your suggestion, I'll try that when I get home this evening) 'rng.Parent.Cells(LastRow + 1, LastCol + 2).formula = "= ..." ' what that resulted in was a "literal" copy of the formula to each row & cell added. Meaning the value returned to the Cell was the same for Row 16 (which would be correct) .. and then 17, 18, 19, etc. It did not "update" the formula for the correct row source. — Phil D.
@Cyril - I think you're following correctly. I had tried using 'rng.Parent.Cells(LastRow + 1, LastCol + 1).formula = "=$B$13 * $C$13" ' yet when I create a new row with 4 new cells, the formula remains calculating for B13 * C13. It does not update based on the "new" row & cells added. — Phil D.
I believe parent calls are antiquated (2003 version maybe?) and might be what is giving this issue. Additionally, if the calculation is put in after the rows have been made, then it will always reply with the original cells. Just given what I'm reading, it sounds that either the parent call is pulling the original cell location or you have the formula entering AFTER the new col/row have been generated (seeing it as the last thing to happen in your code). Not solutions, but food for thought. — Cyril

Cyril Cyril · Accepted Answer · 2017-01-03T20:40:57

Going to try pasting some code I've got that I'm hoping will be a good example:

Private Sub ExportMath()

Dim LR As Long
    LR = Cells(Sheets("Export Report").Rows.Count, 1).End(xlUp).Row

Sheets("Export Report").Range("O2").Formula = "=NETWORKDAYS(C2,D2)"
Sheets("Export Report").Range("O2:O" & LR).FillDown 

End Sub

In this code, I define my last row only, I add a formula to be utilized, then fill it down the entire column.

If you have a defined column you will be working with (even if you insert via .Insert Shift:=xlToRight or .Insert Shift:=xlDown), you will be able to code for a specific column name. In this particular case, I apply this formula AFTER all spreadsheet manipulation has taken place; however, if I were to place this calculation in before I start inserting/deleting rows/columns, the named cells (O2:O) will be updated by Excel.

Updating cell formula based on added cell/row value

1 Answers