Don't show The formula in cell-CodePudding

I have the following formula which works well for me, but I don't want the complete formula to be shown, I just want the RESULT to be shown. I'm stuck with no ideas, could you help me?

This is my code which works fine:

Dim LastR As Integer

With Worksheets("DashBoard")
   LastR = .Cells(Rows.Count, "C").End(xlUp).Row
  .Range("D9").FormulaArray = "=SUM(IF(StoreData!$A$2:$A$10000=$E$3,IF(YEAR(StoreData!$C$2:$C$10000)=C9,StoreData!$B$2:$B$10000)))"
  .Range("D9").AutoFill Destination:=.Range("D9:D" & LastR)
End With

I made this other code so that it only shows the result of the formula, but when I run it, all cells have the same value.

Dim Hjs As Worksheet
Set Hjs = ActiveSheet

With Hjs.Range("D9:D" & Hjs.Cells(Rows.Count, "C").End(xlUp).Row)
    .FormulaArray = "=SUM(IF(StoreData!$A$2:$A$10000=$E$3,IF(YEAR(StoreData!$C$2:$C$10000)=C9,StoreData!$B$2:$B$10000)))"
    .Value = .Value
End With

As I said at the beginning, I only want the result of the formula to be displayed.

Thank you in advance for your support.

CodePudding user response：

Is this what you are trying? Use R1C1 reference style rather than A1 reference style when setting an array formula. Makes your life easier.

Option Explicit

Sub Sample()
    Dim Hjs As Worksheet
    Dim lRow As Long
    
    Set Hjs = ActiveSheet
    
    With Hjs
        lRow = .Range("C" & .Rows.Count).End(xlUp).Row
        
        With .Range("D9:D" & lRow)
            .Formula = "=SUM(IF(StoreData!$A$2:$A$10000=$E$3,IF(YEAR(StoreData!$C$2:$C$10000)=C9,StoreData!$B$2:$B$10000)))"
            .FormulaArray = .FormulaR1C1
            .Value = .Value
        End With
    End With
End Sub

CodePudding user response：

AutoFill an Array Formula (VBA)

Option Explicit

Sub AutoFillArrayFormula()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code

    Dim sLast As Long
    With wb.Worksheets("StoreData")
        sLast = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    Dim dLast As Long
    Dim dFormula As String
    
    With wb.Worksheets("DashBoard")
        dLast = .Cells(.Rows.Count, "C").End(xlUp).Row
        dFormula = "=SUM(IF(StoreData!A$2:A$" & sLast _
            & "=$E$3,IF(YEAR(StoreData!C$2:C$" & sLast _
            & ")=C9,StoreData!B$2:B$" & sLast & ")))"
        With .Range("D9:D" & dLast)
            .Cells(1).FormulaArray = dFormula
            .Cells(1).AutoFill Destination:=.Offset
            .Value = .Value
        End With
    End With

End Sub