我有以下公式對我很有效，但我不想顯示完整的公式，我只想顯示結果。我沒有想法，你能幫我嗎？
這是我的代碼，作業正常：
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
我制作了另一個代碼，以便它只顯示公式的結果，但是當我運行它時，所有單元格都具有相同的值。
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
正如我一開始所說，我只想顯示公式的結果。
預先感謝您對我們的支持。
uj5u.com熱心網友回復：
這是你正在嘗試的嗎？設定陣列公式時使用R1C1參考樣式而不是A1參考樣式。讓您的生活更輕松。
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
uj5u.com熱心網友回復：
自動填充陣列公式 (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
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/388343.html
上一篇：VBA如何在行中添加下一個日期