Expected end of statement error while i am trying

Question

ActiveCell.FormulaR1C1 = "=SUM(Range(Str_rng &"2"&":"& Str_rng & finalOlastRow-1)"

In the above code Str_rng gives column address and value from 2 nd row to last row -1 have to be added in the last row . However it is throwing Expected end of statement error.

Answer 1

The correct line of code should be:

ActiveCell.FormulaR1C1 = "=SUM(" & Str_rng & "2:" & Str_rng & finalOlastRow - 1 & ")"

Just to demystify the solution here is why:

The yellow parts (between the quotations) will be entered into the formula as is (no changes). The green portions must be letters to indicate the columns. The blue portion must be a number to indicate the ending row (for the sum). Also, it must be a number because it is used in a calculation (from which 1 is deducted).

So, assuming that Str_rng = "E" and that finalOlastRow = 21 the above formula converts to:

"=SUM(" & "E" & "2:" & "E" & 20 & ")"

which is equal to

"=SUM(E2:E20)"

Note, that the number 20 is not a string (text). Yet, VBA automatically detects that you want to add a number to a text and converts the number to text (to make the concatenation possible).