VBA-.formula中的变量 [英] VBA -- variable in .formula

问题描述

是否有更优雅(更简单)的方式将变量放入.formula中?我不想使用.formulaR1C1

is there a more elegant (simpler) way to put a variable in .formula? I don't want to use .formulaR1C1

我有此代码:

Range("C8").Select
Selection.End(xlDown).Select
PosR = ActiveCell.Row
KonR = PosR - 2
Range("N" & PosR).Select
aAddress = Range("$N$9").Address & ":" & Range("$N$" & KonR).Address
ActiveCell.Formula = "=SUM(" & aAddress & ")"

很显然，我要将=SUM($N$9:$N$101)(101是最后一个单元格减去2)放入该单元格，此代码即可完成工作.但我只想确保这是最简单的方法.

Obviously I want to put =SUM($N$9:$N$101) (101 is the last cell minus 2) into that cell and this code does the job. But I just want to be sure that this is the easiest way to do this.

推荐答案

最简单的方法是跳过所有选择和那些变量

The easiest way is to skip all that selecting and those variables

PosR = Range("C8").End(xlDown).Row
Range("N" & PosR).Formula = "=SUM($N$9:$N$" & PosR - 2 & ")"

更明确地说，最简单的方式是使用FormulaR1C1，但是您说过您不想这么做，所以...

to be more explicit, the easiest way is to use FormulaR1C1 but you said you didn't want to, so...

这篇关于VBA-.formula中的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！