Don't show the formula in a cell

user_15893004 0
Viewed 73

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. How can I do it?

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

I only want the result of the formula to be displayed.

excel-formula vba excel
2 Answers

Try this. 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
user_11405790

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
user_98140690
Related