将范围变量传递到Excel宏中的公式中 [英] Passing Range Variable into formula in Excel Macro

问题描述

我试图在宏公式中使用变量以避免选择单元格，但是我一直遇到错误.

Im trying to use variables in macro formulas to avoid selecting cells, however i keep getting an error.

这里是我想做的事情的简化:

Here is a simplification of what im trying to do:

    Dim myRange as Range
    Dim formulaCell as Range

    Set myRange = [a1:a10]
    formulaCell.Formula = "=sum(myRange)"

但是我不断得到:

"application-defined or object defined error"

我尝试使用:

formulaCell.Formula = "=sum(" & myRange & ")"

但是我得到了:

Type mismatch

也尝试过:

formulaCell.Formula = "=sum(" & Range(myRange) & ")"

无济于事

我肯定答案是非常基本的，但无法解决我做错了的事情.任何帮助都将不胜感激.

Im sure the answer is very basic but cannot work out what i am doing wrong. Any and all help would be greatly appreciated.

推荐答案

首先，您必须将值设置为formulaCell.然后使用下一个字符串代替您的

At first you must set value to formulaCell. Then use next string instead of yours

formulaCell.Formula = "=sum(" + myRange.Address + ")"

这篇关于将范围变量传递到Excel宏中的公式中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！