CODEWITHSUNDEEP
excelvba
EngineerInNameOnly
EngineerInNameOnly

Reputation: 1

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!

Upvotes: 0

Views: 141

Answers (2)

norie
norie

Reputation: 9857

You could use R1C1 notation for the formula.

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

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166126

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

Upvotes: 0

Related Questions