VBA "Expected end of statement"

Question

I'm fairly new to VBA and need some help with the following syntax.

Dim sumofamount As Variant
sumofamount = WorksheetFunction.Sum(Worksheets("Output").Range("L2:L" & lastrow))

Worksheets("Output").Range("L" & lastrow + 3).FormulaR1C1 = "" = "" & sumofamount & " ""*(1-"" R[-2]C[0] "")-"" R[-1]C[0] "

I'm trying to get a formula into the cell. It also has a variable that I calculated using VBA functions.

How do I combine it with regular Excel formula into the cell? I'm also using relative references because the input cells are dynamic.

Very simply, the code I want to achieve in excel is the following where L21 and L22 are the relative referenced cells.

=sumofamount*(1-L21)-L22

try Worksheets("Output").Range("L" & lastrow + 3).Formula = "=" & sumofamount & "*(1-L21)-L22" — Maddy
Please show us full code , have you initialized lastrow variable ? — Imran Malek
@Maddy That doesn't make any sense because he said: "I'm also using relative references because the input cells are dynamic". So a fixed reference to L21/L22 doesn't help here. — Pᴇʜ

Pᴇʜ Pᴇʜ · Accepted Answer · 2018-12-11T08:44:17

You must use the quotes correctly:

.FormulaR1C1 = "=" & sumofamount & "*(1-R[-2]C[0])-R[-1]C[0]"

VBA "Expected end of statement"

1 Answers