'Adding a formula with a variable row refernce

I'm using populating a sheet using rows.count to add data to the next available row. I'm looking for a way to add a formula to each row but I'm struggling on how to make it work since the formula can't use a definitive row location. My column will always be "G" but the row is going to be sequential as new rows are populated. Any suggestions on how to get this to work? I'm using the below "irow" code to find the row to populate.

Dim irow As Long
irow = Sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1

'part data
With Sheet2
    .Range("A" & irow).Value = Me.TextBox1.Value
    .Range("B" & irow).Value = Me.TextBox2.Value

In this case, I'm trying to add column "N" and "O".

Any advice would be appreciated!

excel vba

Solution 1:^[1]

You could use R1C1 notation for the formula.

.Range("G" & irow).FormulaR1C1 = "=RC14+(RC15*7)"

Solution 2:^[2]

This can work if the formulas contain tokens which can be replaced with the actual row number:

eg: =A<r>+(B<r>*7)

With Sheet2.Cells(rows.count, "A").end(xlUp).offset(1).Entirerow
    .Columns("A").Value = Me.TextBox1.Value
    .Columns("B").Value = Me.TextBox2.Value
    '......
    .Columns("F").Formula = Replace("=A<r>+(B<r>*7)","<r>",.Row) 'for example
end with

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	norie
Solution 2

'Adding a formula with a variable row refernce

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]