Search code examples
excelexcel-2007vba

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.

Solution

  • First you must set formulaCell to refer to a Range. Then use next string instead of yours

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