当我尝试对动态增加的列的值求和时，预期的语句结尾错误 [英] Expected end of statement error while i am trying to sum values of a dynamically increasing column

问题描述

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

在上面的代码中，Str_rng给出了列地址和从第二行到最后一行的值，必须在最后一行添加-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.

推荐答案

正确的代码行应为:

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

仅揭露此解决方案的神秘之处是原因:

Just to demystify the solution here is why:

引号之间的黄色部分将按原样输入到公式中(不变).绿色部分必须为字母以指示列.蓝色部分必须是一个数字，以指示末尾的行(总和).另外，它必须是数字，因为它用于计算(从中扣除1).

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).

因此，假设 Str_rng ="E" 和 finalOlastRow = 21 ，上述公式将转换为:

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

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

等于

"=SUM(E2:E20)"

请注意，数字20不是字符串(文本).但是，VBA会自动检测到您要在文本中添加数字，并将数字转换为文本(以使串联成为可能).

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).

这篇关于当我尝试对动态增加的列的值求和时，预期的语句结尾错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！