Sum formula with variable

[camandab]

Excel 2002

I am trying to reference a range of cells using a variable and then put the sum of that range in another cell. Below is my code:

Dim Nett As Range
   Dim MRange As Range
   Dim MFormula As String
   
    
   Set Nett = Cells.Find(what:="nett.", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Offset(1, 0)
    Set MRange = Range(Nett, Selection.End(xlDown))
    [COLOR=Blue]MFormula = "=Sum(MRange)"[/COLOR]
    Range("L1772").FormulaR1C1 = MFormula

The line in blue is where I'm having trouble. Once the macro finishes and I look in cell L1772, all is says is =SUM(MRange). I've tried the formula as
MFormula = "=Sum(" & MRange & ") but get an error.

I can't figure out the correct syntax or if I'm even able to create this formula.

Once I figure out this formula, I'll need to add the variable to another formula.

FormulaR1C1 = "=SUM(R3C13:R[-2]C)" <--the original formula
FormulaR1C1 = "=SUM(R3C13:R[-2]C) + MRange" <--formula I'd like to end up with

Any help will be much appreciated!

 

[xld]

Dim Nett As Range
   Dim MRange As Range
   
    
   Set Nett = Cells.Find(what:="nett.", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Offset(1, 0)
    Set MRange = Range(Nett, Selection.End(xlDown))
    Range("L1772").Formula = "=Sum(" & MRange.Address & ")"

 

[camandab]

Perfect! I got past the part with the formula but now I'm getting Run-time error 1004: Application-defined or object-defined error on this line:

Range("L1772").FormulaR1C1 = MFormula


I have MFormula declared as a string so I can't figure out why I'm getting the error. Any ideas?

 

[camandab]

Had a duh moment....can't use formular1c1 without r1c1 syntax in the formula

 

[xld]

You can use R1C1 like so

Dim Nett As Range
   Dim MRange As Range
   
    
   Set Nett = Cells.Find(what:="nett.", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Offset(1, 0)
    Set MRange = Range(Nett, Selection.End(xlDown))
    Range("L1772").FormulaR1C1 = "=Sum(" & MRange.Address(,,xlR1C1) & ")"