問題描述
將範圍變量傳遞給 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)"
However i keep getting:
"application-defined or object defined error"
I have tried using:
formulaCell.Formula = "=sum(" & myRange & ")"
but then i get:
Type mismatch
Also tried:
formulaCell.Formula = "=sum(" & Range(myRange) & ")"
to no avail
Im sure the answer is very basic but cannot work out what i am doing wrong. Any and all help would be greatly appreciated.
參考解法
方法 1:
First you must set formulaCell to refer to a Range. Then use next string instead of yours
formulaCell.Formula = "=sum(" & myRange.Address & ")"