VBA Sumifs to paste result as values

Question

Would like to know how to code properly a SUMIFS formula and paste the results as value only. Is it also possible to just have the formula loop only on blank cells? I tried running the code and it seems that is pastes the formula for all cells. I've attached a sample code which I only got from other forums for reference. Would really appreciate your help guys!

Option Explicit

Sub SumGroups()
    Dim lastCode As Long, lastFiltCode As Long

    'Determine Last Row in Column O (Unfiltered Codes)
    With Worksheets("Database")
        lastCode = .Range("O" & .Rows.Count).End(xlUp).Row
    End With

    With Worksheets("Sheet3")
        'Determine last Row in Column A (Filtered Codes)
        lastFiltCode = .Range("A" & .Rows.Count).End(xlUp).Row

        .Range("B2:K" & lastFiltCode).Formula = _
            "=SUMIFS(Database!$M$2:$M$" & lastCode & ",Database!$O$2:$O$" & lastCode & ",$A2,Database!$I$2:$I$" & lastCode & ",B$1)"
    End With

End Sub

Answer 1

You just have to have the work occur in VBA:

cells(1,1).value = Application.SumIfs(Range(Cells(1,1),Cells(10,1)),Range(Cells(1,2),Cells(10,2)), "<0")

Or, you can do this AFTER you have shown the formulas, so the value of the formula is pasted in place of the formula:

cells(1,1).value = cells(1,1).value

---

Edit1:

To point out why your code is entering the formula, by using .Formula = "", you are telling the system to display within the defined range that exact information. These each enter whatever is within the quotations to the cell:

cells(1,1).value = "Cat"
cells(1,1).formula = "=A1+B1"
cells(1,1).formula = "=A" & i & "+B" & i 'where i is a variable