CODEWITHSUNDEEP
vbacellformula
brandon
brandon

Reputation: 11

adding the formula to the excel cell referencing a range

In the following code I am able to add the result of the formula into the cell, however I need the formula in the cell so when I change the value of the range after the macro runs, the value of that cell updates.

In the code below this is the line I'm having trouble with.

Cells(i, LastCol).Value = Application.WorksheetFunction.SumIf(Sourcerange, "<=" & Cells(2, 2), FillRange)

 Set Sourcerange = Range("G3", Cells(3, LastCol))
    Range("G65536").End(xlUp).Select
    Set wf = Application.WorksheetFunction
    Set r = ActiveCell.Offset(0, LastCol)
    r.Select
    Set r = ActiveCell.Offset(0, -5)
    r.Select
    Set rAbove = Range(r, Cells(2, r.Column))
    RowCount = Range(r, Cells(2, r.Column)).Count
    LastCol = r.Column
    FillRange.Select
    For i = 6 To RowCount + 1
        Set FillRange = Range(Cells(i, 7), Cells(i, LastCol))
        FillRange.Select
        Cells(i, LastCol).Value = Application.WorksheetFunction.SumIf(Sourcerange, "<=" & Cells(2, 2), FillRange)
    Next i

Thanks! Brandon

Upvotes: 1

Views: 99

Answers (1)

Vasily
Vasily

Reputation: 5782

try this

Cells(i, LastCol).Formula = "=SUMIF(" & SourceRange.Address & ",""<=""&" & Cells(2, 2).Address & "," & FillRange.Address & ")"

Upvotes: 2

Related Questions