VBA - posting a formula to use the next row

[Evapar18]

I hope this makes sense. I am want to be able to copy a formula to a cell, but I need it to use the next row number.

See below:

If TextBox1 = Enabled And TextBox2 = Enabled Then

With Sheets("Sheet1")

rw = .Range("B" & .Rows.Count).End(xlUp).Row + 1

.Range("P" & rw).Value = TextBox1
.Range("Q" & rw).Value = TextBox2
.Range("E" & rw).Value = "=C9-P9"
.Range("F" & rw).Value = "=C9-Q9"

End With

The Column won't change, but the row number will change. How can I copy this simple formula to use the active row this is copied too?

Thanks,
SB

 

[Norie]

Do you mean you want the row number in the formula to be the same as the row number the formula is going in?

e.g. if it was going in row 10 the first formula would be =C10-P10?

With Sheets("Sheet1")

    rw = .Range("B" & .Rows.Count).End(xlUp).Row + 1

    .Range("P" & rw).Value = TextBox1
    .Range("Q" & rw).Value = TextBox2
    .Range("E" & rw).FormulaR1C1 = "=RC[-2]-RC[11]"
    .Range("F" & rw).FormulaR1C1 = "=RC[-3]-RC[11]"

End With

 

[steve the fish]

You could use this to replace both lines:

.Range(.Cells(rw, "E"), .Cells(rw, "F")).FormulaR1C1 = "=RC3-RC[11]"

 

[Evapar18]

That's correct. Thank you that worked!
SB